asked on

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

ASKER CERTIFIED SOLUTION

Jim Horn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Brian Crowe

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

awking00

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;

Brian Crowe

lol it's the exact same thing cte just makes it easier to read.

wilko100

ASKER

Excellent, nice and simple solution, works great thanks

Jim Horn

Thanks for the grade. Good luck with your project. -Jim