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