Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • SQL : How to get last N records based on ordering of a specific column in SQL Server ?

    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 374
    Comment on it

    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
    

 0 Comment(s)

Sign In
                           OR                           
                           OR                           
Register

Sign up using

                           OR                           
Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: