Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL Server SELECT LAST N Rows

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;

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

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

If you want to select last numbers of rows from a table.

Syntax will be like

 select * from table_name except select top 
 (numbers of rows - how many rows you want)* from table_name

These statements work but differrent ways. thank you guys.

 select * from Products except select top (77-10) * from Products

in this way you can get last 10 rows but order will show descnding way

select top 10 * from products
 order by productId desc 

 select * from products
 where productid in (select top 10 productID from products)
 order by productID desc

 select * from products where productID not in 
 (select top((select COUNT(*) from products ) -10 )productID from products)

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)


First you most get record count from

 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 

Is "Id" indexed? If not, that's an important thing to do (I suspect it is already indexed).

Also, do you need to return ALL columns? You may be able to get a substantial improvement in speed if you only actually need a smaller subset of columns which can be FULLY catered for by the index on the ID column - e.g. if you have a NONCLUSTERED index on the Id column, with no other fields included in the index, then it would have to do a lookup on the clustered index to actually get the rest of the columns to return and that could be making up a lot of the cost of the query. If it's a CLUSTERED index, or a NONCLUSTERED index that includes all the other fields you want to return in the query, then you should be fine.


Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!