SQL Server SELECT LAST N Rows

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

> 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

[1]:

[To see links please register here]

First you most get record count from <Your_Table>

Declare @TableRowsCount Int
select @TableRowsCount= COUNT(*) from <Your_Table>

And then :

In SQL Server 2012

SELECT *
FROM <Your_Table> As L
ORDER BY L.<your Field>
OFFSET <@TableRowsCount-@N> ROWS
FETCH NEXT @N ROWS ONLY;


In SQL Server 2008

SELECT *
FROM
(
SELECT ROW_NUMBER() OVER(ORDER BY ID) AS sequencenumber, *
FROM <Your_Table>
Order By <your Field>
) AS TempTable
WHERE sequencenumber > @TableRowsCount-@N

A technique I use to query the **MOST RECENT** rows in very large tables ***(100+ million or 1+ billion rows)*** is limiting the query to "reading" only the most recent "N" percentage of RECENT ROWS. This is real world applications, for example I do this for non-historic Recent Weather Data, or recent News feed searches or Recent GPS location data point data.

This is a **huge performance improvement** if you know for certain that your rows are in the most recent TOP 5% of the table for example. Such that even if there are indexes on the Tables, it further limits the possibilites to only 5% of rows in tables which have 100+ million or 1+ billion rows. This is especially the case when Older Data will require **Physical Disk** reads and not only **Logical In Memory** reads.

This is well more efficient than SELECT TOP | PERCENT | LIMIT as it does not select the rows, but merely limit the portion of the data to be searched.

DECLARE @RowIdTableA BIGINT
DECLARE @RowIdTableB BIGINT
DECLARE @TopPercent FLOAT

-- Given that there is an Sequential Identity Column
-- Limit query to only rows in the most recent TOP 5% of rows
SET @TopPercent = .05
SELECT @RowIdTableA = (MAX(TableAId) - (MAX(TableAId) * @TopPercent)) FROM TableA
SELECT @RowIdTableB = (MAX(TableBId) - (MAX(TableBId) * @TopPercent)) FROM TableB

SELECT *
FROM TableA a
INNER JOIN TableB b ON a.KeyId = b.KeyId
WHERE a.Id > @RowIdTableA AND b.Id > @RowIdTableB AND
a.SomeOtherCriteria = 'Whatever'

I tested JonVD's code, but found it was very slow, 6s.

This code took 0s.

SELECT TOP(5) ORDERID, CUSTOMERID, OrderDate
FROM Orders where EmployeeID=5
Order By OrderDate DESC

In a very general way and to support SQL server here is

SELECT TOP(N) *
FROM tbl_name
ORDER BY tbl_id DESC

and for the performance, it is not bad (less than one second for more than 10,000 records On Server machine)

use desc with orderby at the end of the query to get the last values.

MS doesn't support LIMIT in t-sql. Most of the times i just get MAX(ID) and then subtract.

select * from ORDERS where ID >(select MAX(ID)-10 from ORDERS)

This will return less than 10 records when ID is not sequential.

I stumpled acros this issue while using SQL server
What i did to resolve it is order the results descending and giving row number to the results of that, After i filtered the results and turned them around again.

SELECT *
FROM (
SELECT *
,[rn] = ROW_NUMBER() OVER (ORDER BY [column] DESC)
FROM [table]
) A
WHERE A.[rn] < 3
ORDER BY [column] ASC

**Easy copy paste answer**

You can get SQL server to select the last N rows with the following query:

select * from tbl_name order by id desc limit N;

In order to get the result in ascending order


```sql
SELECT n.*
FROM
(
SELECT *
FROM MyTable
ORDER BY id DESC
LIMIT N
) n
ORDER BY n.id ASC
```