max row for each id

Question

Post reply

max row for each id

Charbel-371270

SSCrazy

Points: 2260
More actions
July 27, 2007 at 7:58 am

#125130

hi
i have this table like this

ID    INFO    DAte
----------------------
1        A         03/07/07
1        B          09/07/07
1         C         11/07/07
2         AB        11/07/07
2        QC          12/07/07
4          d           05/07/07

i need to get for each ID the entire record for the max(Date)
so i have
1       c      11/07/07
2       QC     12/07/07
4        d       05/07/07
i think its easy and stupid
but im stuck on it
plz help
If something's hard to do, then it's not worth doing.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

AMIT GUPTA-263376 SSCarpal Tunnel Points: 4244 More actions · Answer 1

hI ,

Try this...

SELECT ID_T,MAX(INFO),MAX(DT) FROM IQ_1

GROUP BY ID_T

Thanks

Amit

Charbel-371270 SSCrazy Points: 2260 More actions · Answer 2

10x for ur reply

but i gave a simple example here

the real case is that the row contains many fields , some are int, some are varchar....

so i need that whole row , its not like this simple example

imagine this example with many other rows like info (int and varchar...)

10x

If something's hard to do, then it's not worth doing.

John Rowan SSC Guru Points: 56440 More actions · Answer 3

This may get you going in the right direction. Keep in mind that in the event you have 2 rows in your table with the same MAX(Date), you'll get more that one row back for that ID. We really don't know from your post if that can happen here, but if you think it can, you'll need to define what you would want to do as a tie-breaker in that scenario.

declare @table table (ID int, INFO varchar(10), DAte datetime)

insert into @table

select 1, 'A', '03/07/07' union all

select 1, 'B', '09/07/07' union all

select 1, 'C', '11/07/07' union all

select 2, 'AB', '11/07/07' union all

select 2, 'QC', '12/07/07' union all

select 4, 'd', '5/07/07'

SELECT t.*

FROM @table t

INNER JOIN (

SELECT ID, MAX(Date) as MaxDate

FROM @table

GROUP BY ID

) t2

ON t.ID = t2.ID AND t.Date = t2.MaxDate

ORDER BY t.ID

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Charbel-371270 SSCrazy Points: 2260 More actions · Answer 4

for a specific ID i have one and only one max (ID)

insert into @table

select 1, 'A', '03/07/07' union all

select 1, 'B', '09/07/07' union all

select 1, 'C', '11/07/07' union all

select 2, 'AB', '11/07/07' union all

select 2, 'QC', '12/07/07' union all

select 4, 'd', '5/07/07'

again this is a sample table

i donno how may records i have

if i have a million, i will do this million time? ?

If something's hard to do, then it's not worth doing.

John Rowan SSC Guru Points: 56440 More actions · Answer 5

I'm not sure if I'm following you. Do what a million times? The solution I provided will obviously need re-written to use your real table/column names, but it will work against your entire table. Nothing will need done multiple times. Also, I don't want to sound condescending here, but the whole DECLARE @table...INSERT INTO @table piece of the code I posted was just to give me some sample data to work with based off of your original post. What you want is the SELECT statement.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden