CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Contango
Contango

Reputation: 80428

TSQL to select the last 10 rows from a table?

I have a table that contains 300 million rows, and a clustered index on the [DataDate] column.

How do I select the last 10 rows of this table (I want to find the most recent date in the table)?

Database: Microsoft SQL Server 2008 R2.

Update

The answers below work perfectly - but only if there is a clustered index on [DataDate]. The table is, after all, 300 million rows, and a naive query would end up taking hours to execute rather than seconds. The query plan is using the clustered index on [DataDate] to get results within a few tens of milliseconds.

Upvotes: 6

Views: 28965

Answers (3)

ba__friend
ba__friend

Reputation: 5913

TOP 

SELECT TOP(10) [DataDate] FROM YourTable ORDER BY [DataDate] DESC

Upvotes: 14

Akram Shahda
Akram Shahda

Reputation: 14781

TOP (Transact-SQL) specifies that only the first set of rows will be returned from the query result. The set of rows can be either a number or a percent of the rows. The TOP expression can be used in SELECT, INSERT, UPDATE, MERGE, and DELETE statements.

SELECT TOP(10) *
FROM MyTable
ORDER BY DataDate DESC

Upvotes: 12

devuxer
devuxer

Reputation: 42394

Do a reverse sort using ORDER BY and use TOP.

Upvotes: 2

Related Questions