How to Select LAST N Row in SQL Server


I am not very much efficient in SQL Query so wanted to know how to fetch last N records or rows from a table using query in SQL Server.

Like, we can do using TOP keyword in SQL Server to fetch first 10 results as below

SELECT TOP N *
FROM MyTable

Thanks


Asked by:- neena
0
: 643 At:- 11/28/2022 4:19:29 PM
SQL select last N records







1 Answers
profileImage Answered by:- vikas_jk

You can use this query to get last N records

SELECT TOP(N) *
FROM TableName
ORDER BY Table_ID DESC

OR

simply use this query syntax to get last N records

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

So for example, I have a table with total 7 rows, to get last 2 records in SQL Server, query would be as below:

select * from [OrderDetails].[dbo].[EmployeeSalary] except select top 
(7 - 2)* from [OrderDetails].[dbo].[EmployeeSalary]

If you don't know how many total records are there in table

select * from [AdventureWorks2012].[Production].[Product] 
except 
select top 
 ((SELECT COUNT(*) FROM [AdventureWorks2012].[Production].[Product]) - 2)
 * from [AdventureWorks2012].[Production].[Product]

That should also work.

OR

In SQL Server 2012 or above try this

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

It will also work.

 
1
At:- 11/28/2022 4:38:32 PM Updated at:- 5/18/2023 9:50:19 AM
Thanks for perfect detailed answer. I tried all variations looks to be working fine for me but first one is simple query. 0
By : neena - at :- 11/28/2022 4:41:32 PM






Login/Register to answer
Or
Register directly by posting answer/details

Full Name *

Email *




By posting your answer you agree on privacy policy & terms of use