how to get the distinct records based on maximum date?

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

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)
        )

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

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.