A SQL Server database has 2 tables:

• Department: Id
• Employee: Id, Department, Employee_Name, Date_Hired

I wanted to display maximum 2 employees hired the latest in 2021 per department

For example

The output should be

I have this SQL:

SELECT e.* 
FROM Employee e
WHERE e.Date_Hired IN (SELECT e.Date_Hired
                      FROM Employee e
                      WHERE YEAR(e.Date_Hired) = 2021)
ORDER BY e.Date_Hired DESC

But it is displaying all 3 items from department 1.

Thanks in advance!

CodePudding user response：

SELECT e.*
FROM Employee e,
     (SELECT id,
             Date_Hired,
             row_number() OVER(PARTITION BY Department ORDER BY id DESC) AS rn
      FROM Employee e
      WHERE YEAR (e.Date_Hired) = 2021) sub
WHERE rn <= 2
  and e.id = sub.id
ORDER by e.Date_Hired DESC

CodePudding user response：

If your relation had integer rank numbers describing each employee's position within a department, then a simple WHERE clause could easily request the top K employees.

You are looking for the rank query function: https://docs.microsoft.com/en-us/sql/t-sql/functions/rank-transact-sql?view=sql-server-ver16

The relationship among RANK, DENSE_RANK, and ROW_NUMBER is perhaps more subtle than your unit tests and future maintainers would care to consider.