SQL Query Question

I have the following table "employees"

deptid              employeeid            datejoined
1                              1                        1/1/2000
1                              2                        2/1/2000
1                              3                        3/1/2000
-                              -                              -
-                              -                              -
2                              6                        1/1/2001
2                              7                        2/1/2001
2                              8                        3/1/2001
-                              -                              -
-                              -                              -
-                              -                              -

And so on.
Now I would like to get the  any first 2 employees for each dept based on thier date joined. What is the best way to go about doing this?
I cannot specify any dates, but I need to get the first 2 employees in each dept.
The query could be quite simple, but for some reason I am not getting it right. I am using SQL Server 2000
Any thoughts?
Thanks.

Anon
Saturday, January 31, 2004

SELECT
    DeptId,
    EmployeeId,
    DateJoined
FROM
    Employees
WHERE
    EmployeeId IN
    (
        SELECT TOP 2
            EmployeeId
        FROM
            Employees TopEmployees
        WHERE
            TopEmployees.DeptId = Employees.DeptId
        ORDER BY
            DateJoined DESC
    )

RC
Saturday, January 31, 2004

Thanks RC, it looks like thats what I want. I have yet to double check the result set to see if the data returned is correct.
I knew I had to use sub-select statements. But I wasn't getting it right. Thanks again.

Anon
Saturday, January 31, 2004

  Recent Topics

  Fog Creek Home