CODEWITHSUNDEEP
sqlt-sql
Saobi
Saobi

Reputation: 17011

How to construct this query in T-SQL

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.

Upvotes: 1

Views: 104

Answers (2)

Quassnoi
Quassnoi

Reputation: 425261

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:

Upvotes: 2

rosscj2533
rosscj2533

Reputation: 9323

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

Upvotes: 9

Related Questions