Search content within the blog

Friday, April 17, 2009

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

No comments:

Post a Comment