How to construct this query in T-SQL

Question

I have a table:

Date        ColumnA    ColumnB
2009-12-29    "abc"     "abc"
2009-12-29    "abc"     "abc"
2009-12-29    "abc"     "abc"
2009-12-28    "abc"     "abc"
2009-12-28    "abc"     "abc"
2009-12-28    "abc"     "abc"
,,,
,,,

I want to write a query, in Microsoft SQL, that returns all rows for the latest available date in the table. So in the above case, it returns all rows with date 12-12-29.

Answer 1

SELECT Date, ColumnA, ColumnB
FROM TableName 
WHERE Date = 
  (SELECT max(Date) FROM TableName)

Answer 2

SELECT  TOP 1 WITH TIES *
FROM    mytable
ORDER BY
        date DESC

This will work even for composite maximums (like, (date, othercolumn), which cannot be retrieved with a plain MAX)

You may want to read these articles in my blog for several ways to do this:

• SQL Server: Selecting records holding group-wise maximum
• SQL Server: Selecting records holding group-wise maximum (with ties)