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

4 comments:

Anonymous said...

Thanks! This was exactly what I was looking for.

Anonymous said...

perfect trick to avoid a cursor

Anonymous said...

Hey Suprotim, nice post. How would I do this if I didn't want to see it by employee, just the last 5 orders inserted into the database? Email me at joseph.larrew@gmail.com please.

Unknown said...

18
17
5
5
4
2
2
4
16
15
8
9
sir
i hve a single column with these values

sir i wnt to find the result last five inserted value (9,8,15,16,4)