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?

Solution 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

Solution 2

If you need only ID column and other columns are NOT required, then you don't need to go with ROW_NUMBER or MAX or anything else. You just do a Group By over ID column, because whatever the maximum date is you will get same ID.

SELECT ID FROM table GROUP BY ID
--OR
SELECT DISTINCT ID FROM table

If you need ID and Date columns with maximum date, then simply do a Group By on ID column and select the Max Date.

SELECT ID, Max(Date) AS Date
FROM table 
GROUP BY ID

If you need all the columns but 1 line having Max. date then you can go with ROW_NUMBER or MAX as mentioned in other answers.

SELECT *
FROM   table AS M
WHERE  Exists(
        SELECT 1
        FROM   table
        WHERE  ID = M.ID
        HAVING M.Date = Max(Date)
        )

Solution 3

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.

Solution 4

select Max(Date) as "Max Date" from table group by Id order by Id

View more solutions