how to get the distinct records based on maximum date?

Question

I'm working with Sql server 2008.i have a table contains following columns,

    Id,
    Name,
    Date

this table contains more than one record for same id.i want to get distinct id having maximum date.how can i write sql query for this?

Answer 1

Use the ROW_NUMBER() function and PARTITION BY clause. Something like this:

SELECT Id, Name, Date FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY Id ORDER BY Date desc) AS ROWNUM 
    FROM [MyTable]
) x WHERE ROWNUM = 1

Answer 2

One way, using ROW_NUMBER:

With CTE As
(
    SELECT Id, Name, Date, Rn = Row_Number() Over (Partition By Id
                                                   Order By Date DESC)
    FROM dbo.TableName
)
SELECT Id --, Name, Date 
FROM CTE
WHERE Rn = 1

If multiple max-dates are possible and you want all you could use DENSE_RANK instead.

Here's an overview of sql-server's ranking function: http://technet.microsoft.com/en-us/library/ms189798.aspx

By the way, CTE is a common-table-expression which is similar to a named sub-query. I'm using it to be able to filter by the row_number. This approach allows to select all columns if you want.