Group By Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Group By Help

Author

Topic

killtacularmania
Starting Member

22 Posts

Posted - 2010-03-25 : 09:16:51

Hey Guys I need some help with a simple group by statement. I am in Sql 2005 my table looks like below. I need to pull the Member Number, the office number, and the number of visits for the record that had the most visits per member number.

Member Number Office Number Number Of Visits
55555555-01 99999 10
55555555-01 88888 12
14444444-02 99999 5
14444444-02 33333 11
14444444-02 88888 7

I need to get to see the following

Member Number Office Number Number Of Visits
55555555-01 88888 12
14444444-02 33333 11

Thanks for your help!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-25 : 09:31:04

select Member, [Number Office ] ,max([ Number Of Visits]) as [ Number Of Visits] from your_table
group by Member, [Number Office ]

Madhivanan

Failing to plan is Planning to fail

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2010-03-25 : 09:35:20

[code]

--Member Number Office Number Number Of Visits
--55555555-01 99999 10
--55555555-01 88888 12
--14444444-02 99999 5
--14444444-02 33333 11
--14444444-02 88888 7

declare @foo table ([Member Number] varchar (20),[Office Number] varchar(10),[Number of Visits] int)

insert into @foo ([Member Number],[Office Number],[Number of Visits])

select '55555555-01', '99999', 10
union all select '55555555-01', '88888', 12
union all select '14444444-02' ,'99999', 5
union all select '14444444-02' ,'33333', 11
union all select '14444444-02' ,'88888', 7

select
a.[Member Number],a.[Office Number],a.[Number of Visits]
from
@foo a
inner join
(select
[Member Number],MAX([Number of Visits]) as [Number of Visits]
from
@foo
group by
[Member Number]) b
on
a.[Member Number] = b.[Member Number]
and
a.[Number of Visits] = b.[Number of Visits]

RETURNS:
Member Number Office Number Number of Visits
55555555-01 88888 12
14444444-02 33333 11

select [Member Number], [Office Number] ,max([Number of Visits] ) as [ Number Of Visits] from @foo
group by [Member Number], [Office Number]

RETURNS:

Member Number Office Number Number Of Visits
14444444-02 33333 11
14444444-02 88888 7
55555555-01 88888 12
14444444-02 99999 5
55555555-01 99999 10

[/code]

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-25 : 09:48:20

Thanks. I didn't see the input properly

Madhivanan

Failing to plan is Planning to fail

killtacularmania
Starting Member

22 Posts

Posted - 2010-03-25 : 10:02:53

Thanks a lot guys!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources