SQL get latest date from records

Hi

I have a single table that has a one to many relationship and i want the get the top, latest date from those records used.

So for example the following records have IDs and against each ID can have multiple records:

ID  Date
1     01/09/2013
1     02/09/2013
1     03/09/2013
2     06/09/2013
2     07/07/2013
2     08/07/2013
2     09/07/2013


I want to return one single record per ID, in this case  id 1 and 2 with the latest date
i.e would return.

ID  Date
1     03/09/2013
2     09/09/2013
wilko100Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
SELECT ID, Max(Date)
FROM YourTable
GROUP BY ID

btw the 09/07/2013 if your example data is not the same as the return set of 09/09/13.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Brian CroweDatabase AdministratorCommented:
If there are more columns associated with the lastest date in your many table that you want returned as well you can use a common table expression and row_number().

WITH cte (ID, Date, columnA, columnB, ..., RowNumber)
AS
(
   SELECT ID, Date, columnA, columnB, ...,
      ROW_NUMBER() OVER (PARTITION BY ID, ORDER BY Date DESC) AS RowNumber
   FROM manyTable
)
SELECT T1.ID, cte.Date, cte.columnA, cte.ColumnB, ...
FROM oneTable AS T1
INNER JOIN cte
   ON T1.ID = cte.ID
   AND cte.RowNumber = 1
0
awking00Information Technology SpecialistCommented:
With CTE and join is not necessary, just -
SELECT ID, Date, columnA, columnB, ... FROM
 (SELECT ID, Date, columnA, columnB, ...,
      ROW_NUMBER() OVER (PARTITION BY ID, ORDER BY Date DESC) AS rn
   FROM manyTable) as x
)
WHERE rn = 1;
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Brian CroweDatabase AdministratorCommented:
lol it's the exact same thing cte just makes it easier to read.
0
wilko100Author Commented:
Excellent, nice and simple solution, works great thanks
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Thanks for the grade.  Good luck with your project.  -Jim
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.