select-last-n-rows-in-a-sql-server-data-table

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
   )