Query to get the row corresp. to the latest date

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 2005 Forums

Transact-SQL (2005)

Query to get the row corresp. to the latest date

Author

Topic

rkannan
Starting Member

1 Post

Posted - 2007-03-13 : 05:17:02

I have the following table

Name Rating Date (DD/MM/YYYY)
ABC A+ 01/01/2005
BCD B 12/04/2006
CDE C 09/08/2007
ABC AA 01/12/2005

I would like to fetch all unique names and their rating. If there are multiple ratings for the same Name then, i should get the rating corrsponding to the latest date. I would want this to be in a SQL Query and not in a stpred proc. Please help!!!!

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2007-03-13 : 05:59:11

[code]SELECT a.Name, a.Rating, a.Date
FROM mytablename a
INNER JOIN (
SELECT Name, MAX(Date) AS Date
FROM mytablename
GROUP BY Name) b
ON a.Name = b.Name AND a.Date = b.Date[/code]

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources