Search code examples
sqlsql-serverperformancesql-server-2008

SQL Server SELECT LAST N Rows

This is a known question but the best solution I've found is something like:

SELECT TOP N *
FROM MyTable
ORDER BY Id DESC

I've a table with lots of rows. It is not a posibility to use that query because it takes lot of time. So how can I do to select last N rows without using ORDER BY?

EDIT

Sorry duplicated question of this one

Solution

  • You can do it by using the ROW NUMBER BY PARTITION Feature also. A great example can be found here:

    I am using the Orders table of the Northwind database... Now let us retrieve the Last 5 orders placed by Employee 5:

    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