Using MAX and GROUPBY - 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

Using MAX and GROUPBY

Author

Topic

pvccaz
Yak Posting Veteran

87 Posts

Posted - 2007-04-23 : 20:47:07

Hi,

I need help in creating a query using MAX and group by.

The input SQL table ClientInfo contains data like the following:

Client Div MaxDt
00003 00 19820501
00003 00 19830208
00003 00 19850801
00003 00 20040811
00003 50 19850101
00003 50 20040811
00003 99 19870131

I need the output to have the max date for every division of every client and output should be as follows:

Client Div MaxDt
00003 00 20040811
00003 50 20040811
00003 99 19870131

Thanks,

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-04-23 : 20:55:45

select client, div, max(maxdt) as MaxDT
from yourtable
group by client, div

Read up on GROUP BY in books online, and practice using it and different aggregate functions to get a feel for it. It is a very crucial and powerful part of SQL to be familiar with. Many SQL books explain it quite well, I recommend checking them out.

- Jeff
http://weblogs.sqlteam.com/JeffS

pvccaz
Yak Posting Veteran

87 Posts

Posted - 2007-04-23 : 21:15:13

Thank you Jeff...

pvccaz
Yak Posting Veteran

87 Posts

Posted - 2007-04-24 : 14:08:07

Hi,

My table structure is:
CLNUM DIV DATE CLNAME
00003 00 20040811 ABC Corporation
00003 00 19830208 Chemical New York Corporation
00003 50 20040811 ABC Corporation
00003 50 19850101 Training Abstract
00003 99 19870131 ABC Corporation
00004 00 19841201 Glaxo, Incorporated
00005 00 19870101 Institute of Nuclear Power Ops
00006 00 19850801 Barclays American Bus Credit

The query should pick up the CLNUM, DIV, DATE, CLNAME of the maximum date. If i am using groupby all the names are coming. I need only the record that the max date.
Is there any other way to write the query.?.. The output should be as follows.

CLNUM DIV DATE CLNAME
00003 00 20040811 ABC Corporation
00003 50 20040811 ABC Corporation
00003 99 19870131 ABC Corporation
00004 00 19841201 Glaxo, Incorporated
00005 00 19870101 Institute of Nuclear Power Ops
00006 00 19850801 Barclays American Bus Credit

Thanks,

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-24 : 14:13:43

select clnum, div, date, clname from
(select *, row_number() over (partition by clnum, div order by date desc) as recid) as a
where recid = 1

Peter Larsson
Helsingborg, Sweden

pvccaz
Yak Posting Veteran

87 Posts

Posted - 2007-04-24 : 14:39:45

Thank you Peter...!!..

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-24 : 14:56:22

Just make sure you include clientinfo table in the derived table.

select clnum, div, date, clname from
(select *, row_number() over (partition by clnum, div order by date desc) as recid from clientinfo) as a
where recid = 1

Peter Larsson
Helsingborg, Sweden

pvccaz
Yak Posting Veteran

87 Posts

Posted - 2007-04-24 : 15:11:48

yes i added the table name and it worked fine.. This is the first time i am using the row_number() feature of SQL.. Just now i read about it and it is really a cool feature... thanks so much...!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources