SQL server Display Name of Max Values

Question

I am trying to find the top 3 sick leavers per Site in my employee table. Im attempting to write a query to find the top sick leaver per site first.

Here is what I have but I can't seem to get it right Here's what I have

select Site.SiteName,
[FullName] where MAX([SickLeaveTaken])
from Employee
left join Site
on(Employee.SiteID=Site.SiteID)
GROUP BY Site.SiteName;
GO

You seem to be aggregating by site, and I'm not sure why you thought to do this. Please include some sample data and the expected output. — Tim Biegeleisen, May 01 '19 at 12:52

score 0 · Answer 1 · answered May 01 '19 at 13:00

0

You can get top 3 employees with max sick leave by using RANK function in SQL

select top 3 EmployeeID,SickLeaveTaken,s.SiteName,rn from 
(select e.EmployeeID,SickLeaveTaken,e.SiteID ,RANK() over(order by [SickLeaveTaken] 
desc ) as rn
from @Employee e )e
left join @Site s on e.SiteID=s.SiteID
order by rn desc

answered May 01 '19 at 13:00

Chirag

31
2

What happens if 5 employees happen to be tied for the most sick leave? How many records would your query return in that case? – Tim Biegeleisen May 01 '19 at 13:00

score 0 · Answer 2 · answered May 01 '19 at 13:02

Assuming that the table Employee contains the column SickLeaveTaken (although this is not normal) you need to sort descending the results and get TOP 3:

select TOP(3) WITH TIES
  s.SiteName, 
  e.FullName, 
  MAX(e.SickLeaveTaken) maxleave
from Site s inner join Employee e
on e.SiteID = s.SiteID
GROUP BY s.SiteName, e.FullName
ORDER BY MAX(e.SickLeaveTaken) DESC;

SQL server Display Name of Max Values

2 Answers2