Geek Answers Handbook

TSQL to select the last 10 rows from a table?

I have a table containing 300 million rows and a clustered index in the [DataDate] column.

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

Database: Microsoft SQL Server 2008 R2.

Update

The answers below work fine - but only if there is a clustered index in [DataDate]. In the end, the table is 300 million rows, and a naive query will take several hours, not seconds. The query plan uses the cluster index in [DataDate] to retrieve results within a few tens of milliseconds.

+4
source share
3 answers

TOP

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

TOP (Transact-SQL) indicates that only the first rowset will be returned from the query result. A rowset can be either a number or a percentage of rows. The TOP expression can be used in SELECT , INSERT , UPDATE , MERGE and DELETE expressions.

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

Reverse sort with ORDER BY and use TOP.

+2
source

More articles:


All Articles