Sometimes we may be required to get the get last N records of a table in SQL server based on ordering on a specific column . In this post we will create a query to get the result. First let us create a table which we will use in our query.

CREATE TABLE #Table1 (EmpID INT, Position VARCHAR(50), CreatedDate datetime)
INSERT INTO #Table1 (EmpID, Position ,CreatedDate)
SELECT 11,'First', '1998-04-21'
UNION ALL
SELECT 11, 'Second','1998-04-22'
UNION ALL
SELECT 11, 'Third','1998-04-23'
UNION ALL
SELECT 11, 'Fourth','1998-04-29'
UNION ALL
SELECT 11, 'Fifth','1998-04-25'
UNION ALL
SELECT 11, 'Sixth','1998-04-26'
UNION ALL
SELECT 11, 'Seventh','1998-04-27'
UNION ALL
SELECT 12, 'Fifth','1998-04-25'
UNION ALL
SELECT 12, 'Sixth','1998-04-26'
UNION ALL
SELECT 12, 'Seventh','1998-04-27'
GO

In the above table , column EmpID contains ID's of employees , column Position contains the rank employee got in an internal exam and CreatedDate is the date of exam.Now let us say we want to get the position employees got in last 5 exams,we need to write query as below :

SELECT EmpID, Position, CreatedDate

FROM

(

SELECT ROW_NUMBER() OVER (PARTITION BY EmpID ORDER BY CreatedDate DESC) AS ID,*

FROM #Table1

) as X

WHERE X.ID <=5