SQL get last three course rows for each employee

Mohammad Alsolaiman
Mohammad Alsolaiman used Ask the Experts™
on
Hi:
I need to have the latest three course  rows for each employee
tblCourse
          empNo
         courseName
         courseEndDate
please help me do so.
note:
I had try "LIMIT" but I encountered "Incorrect syntax near 'LIMIT'".
So please try any solution other than LIMIT
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Obadiah ChristopherDeveloper User Interface

Commented:
Select top 3 empNo, courseName, courseEndDate from tblCourse order by courseEndDate desc
Mohammad Alsolaimanapplication programmer

Author

Commented:
How about the other employees?
I think this will retrive only first three rcirds. While my question is to retrive the last three courses for each employee.
EE Topic Advisor
Most Valuable Expert 2014
Awarded 2013
Commented:
row_number() over(partition by ... order by ... DESC)

This allows you to determined the most recent rows for each "partition", as seen below:
SELECT
    empNo
  , courseName
  , courseEndDate
FROM (
    SELECT
        empNo
      , courseName
      , courseEndDate
      , ROW_NUMBER() OVER (PARTITION BY empNo ORDER BY courseEndDate DESC) AS rn
    FROM tblCourse
    ) d
WHERE rn <= 3

Open in new window

Mohammad Alsolaimanapplication programmer

Author

Commented:
thanks to all for participating answering my question, especially PortletPaul (awesome).

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial