Menu
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.
1000
How to select last 10 records in SQL Server In SQL Server, we can easily select the last 10 records from a table by using the “ SELECT TOP ” statement. The TOP clause in SQL Server is used to control the number or percentage of rows from the result. And to select the records from the last, we have to arrange the rows in descending order.
you can with code select 10 row from end of table. select * from (SELECT * FROM table1 order by id desc LIMIT 10) as table2 order by id" Show activity on this post. If you know how many rows to expect, I would create a separate temporary table in your database of the expected structure, append into that, then check the count...
to get the last row of a SQL-Database use this sql string: SELECT * FROM TableName WHERE id= (SELECT max (id) FROM TableName);
As we know that LIMIT clause gives the no. of specified rows from specifies row. We will retrieve last 5 rows in descending order using LIMIT and ORDER BY clauses and finally make the resultant rows ascending. Since Employee table has IDs, we will perform ORDER BY ID in our query.
TOP
SELECT TOP(10) [DataDate] FROM YourTable ORDER BY [DataDate] DESC
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
Do a reverse sort using ORDER BY and use TOP.
23
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
