Select Top 5 records of every employee in SQL Serv

问题:

I have the following issue, I have this query that select the latest 5 records created for an employee:

SELECT TOP 5
       p.value,
       p.record_date AS FECHA
FROM 
    employee_loan_movements p
WHERE
     p.employee_code = '1'
     AND p.record_date <= '2009-11-11'
     AND p.movement_type = 1
     AND p.value > 0
ORDER BY p.record_date DESC

Now i need to build a query to select the top 5 of every employee in the loan_movements table, I know i can do it in Oracle by selecting selecting rownum and rownum <= 5 but I cant manage to find a way to do that or something else with the same result in SQL Server 2000.

I guess I could use a function but I want to know first if it can be done in plain SQL.

Thanks for the help...

回答1:

Not in SQL 2000, you can not do that. In SQL 2005/8 however you can:

WITH foo(eid) as (select distinct employee_code from employee_loan_movements)
SELECT * FROM foo
CROSS APPLY (
    SELECT TOP 5
        p.value,
        p.record_date AS FECHA
    FROM 
    employee_loan_movements p
    WHERE
      p.employee_code = foo.employee_code
      AND p.record_date <= '2009-11-11'
      AND p.movement_type = 1
      AND p.value > 0
      ORDER BY p.record_date DESC
)

回答2:

I answered before noticing you're on SQL Server 2000, so you couldn't take advantage of ROW_NUMBER. However, this MS Knowledgebase article gives examples how to artificially rank rows.

Once you have the ranking working, use the query in an inline view:

SELECT x.*
  FROM (SELECT p.value,
               ... 'rank'
          FROM EMPLOYEE_LOAN_MOVEMENTS p) x
 WHERE x.rank <= 5