Need help with this query - 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)

Need help with this query

Author

Topic

stix123123
Starting Member

1 Post

Posted - 2007-06-22 : 11:19:23

I am trying to filter my data set based on taking the latest date for records with the same ID so that after the query I have 1 record per identifier. Heres an example

ID Date Data
1 1/1/2006 100
1 7/1/2006 200
1 3/1/2006 300
2 5/1/2006 250
3 4/1/2006 100
3 8/1/2006 150

Desired Output
ID Date Data
1 7/1/2006 200
2 5/1/2006 250
3 8/1/2006 150

Any help on implementing this would be great. Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-22 : 11:32:19

[code]SELECT *
FROM yourtable t
WHERE [Date] = (SELECT MAX([Date]) FROM yourtable x WHERE x.[ID] = t.[ID])
ORDER BY [ID]
[/code]

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-22 : 11:33:34

Select columns from table T1
inner join
(Select id,max(date) as date from table group by id) T2
on T1.id=T2.id and T1.date=T2.date

I think you need to Learn SQL