Select Last N Rows In A SQL Server Data Table
2011-09-20
How to select last 5 or 10 or … rows in a SQL Server data table ?
Solution 1: This is the normal solution
SELECT TOP N * FROM MyTable ORDER BY Id DESC
Solution 2: Solution 1 is the normal way but when the data table is huge but the Id is not indexed, the order operation will spend lots of time.
The following sample uses ROW_NUMBER() and PARTITION, it is from SQL Server Curry
SELECT ORDERID, CUSTOMERID, OrderDate FROM ( SELECT ROW_NUMBER() OVER (PARTITION BY EmployeeID ORDER BY OrderDate DESC) AS OrderedDate,* FROM Orders ) as ordlist WHERE ordlist.EmployeeID = 5 AND ordlist.OrderedDate <= 5
Solution 3: This is smart and clean solution if you do not want to know what ROW_NUMBER and PARTION are, the following sample select last 10 rows:
select * from MyTable where myID not in ( select top ((select count(*) from MyTable) - 10) myID from MyTable )