Group by question - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Group by question

Author

Topic

ch9862
Yak Posting Veteran

76 Posts

Posted - 2007-05-30 : 10:44:27

I have the following table:

declare	@a table (id char(2), seq int, dt datetime, nm varchar(3))
insert	@a
select	'A1', 18, '04/04/1980', 'f1' union all
select	'A1', 10, '01/14/1983', 'f2' union all
select	'A1', 16, '11/26/1980', 'f1' union all
select	'A2', 18, '11/03/1989', 'r3' union all
select	'A2', 17, '02/14/1986', 'r2' union all
select	'A2', 16, '10/23/1981', 'r1' union all
select	'A3', 11, '02/17/1988', 'e5' union all
select	'A3', 24, '06/07/1985', 'e3' union all
select	'A3', 12, '02/17/1988', 'e1'

I need to select one record per ID from it. I need to pick the one with most recent dt, and if there is more than one, with highest seq. I also need nm field for this record.

The trick is that I can't simply select max(dt) and max(seq) - max(seq) for a particular id might be in a record with NOT the most recent date.

In other words I'm expecting:


id  dt          seq  nm
A1  01/14/1983  10   f2
A2  11/03/1989  18   r3
A3  02/17/1988  12   e1

How do I do it?

ch9862
Yak Posting Veteran

76 Posts

Posted - 2007-05-30 : 10:52:32

OK - sorry, I got it. I need to used derived table with id and max(dt), and group the results by seq.
I thought I tried it before and it didn;t work, but must have misspelled something.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-30 : 10:57:37

[code]
declare @a table (id char(2), seq int, dt datetime, nm varchar(3))
insert @a
select 'A1', 18, '04/04/1980', 'f1' union all
select 'A1', 10, '01/14/1983', 'f2' union all
select 'A1', 16, '11/26/1980', 'f1' union all
select 'A2', 18, '11/03/1989', 'r3' union all
select 'A2', 17, '02/14/1986', 'r2' union all
select 'A2', 16, '10/23/1981', 'r1' union all
select 'A3', 11, '02/17/1988', 'e5' union all
select 'A3', 24, '06/07/1985', 'e3' union all
select 'A3', 12, '02/17/1988', 'e1'

SELECT t.*
FROM @a t
JOIN ( SELECT t1.id, t1.dt, MAX(t1.seq) AS seq
FROM @a t1
JOIN (SELECT id, MAX(dt) AS dt
FROM @a
GROUP BY id
) t2 ON t1.id = t2.id AND t1.dt = t2.dt
GROUP BY t1.id, t1.dt
) t3 ON t.id = t3.id AND t.dt = t3.dt AND t.seq = t3.seq
[/code]

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources