SQL get last three course rows for each employee

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
Mohammad Alsolaimanapplication programmerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Obadiah ChristopherDeveloper User InterfaceCommented:
Select top 3 empNo, courseName, courseEndDate from tblCourse order by courseEndDate desc
Mohammad Alsolaimanapplication programmerAuthor 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.
PortletPaulEE Topic AdvisorCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mohammad Alsolaimanapplication programmerAuthor Commented:
thanks to all for participating answering my question, especially PortletPaul (awesome).
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.