Retrieve Last N Rows Based On a Condition in a SQL Server

I was recently working on a report where I had to display the Last 5 orders (by date) placed by an Employee. Here's how I got that data. This query will also work where the total number of records is less than 5:

I am using the Orders table of the Northwind database:

Let's first get all the records placed by Employee 5

SELECT ORDERID, CUSTOMERID, OrderDate

FROM Orders where EmployeeID=5

Order By OrderDate

Now let us retrieve the Last 5 orders placed by Employee 5. I just love the ROW NUMBER BY PARTITION Feature and here's how I will use it:

 

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

Output:

ORDERID    CUSTOMERID    OrderDate

11043    SPECD    1998-04-22 00:00:00.000

10954    LINOD    1998-03-17 00:00:00.000

10922    HANAR    1998-03-03 00:00:00.000

10899    LILAS    1998-02-20 00:00:00.000

10874    GODOS    1998-02-06 00:00:00.000

About The Author

Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook