How to SELECT lines where COUNT DISTINCT is MAX?

user_6612848 0
Viewed 139

this may be an easy question but basically I would like to get to SELECT only the lines of highest number of occurence for an experiment in SQL SERVER. I have a query that produces the following data:

SELECT [JOB ROLE], [CITY], COUNT(DISTINCT([EMPLOYEE_ID])) as [COUNT]
FROM MyTable
GROUP BY [JOB ROLE], [CITY]

Result of existing query

I would like to reach to the following outcome to SELECT only the lines where the COUNT DISTINCT of EMPLOYEE_ID is MAX:

Desired outcome

Many thanks in advance for the kind help and suggestions on how to get here the easiest!

max select group-by sql-server sql
2 Answers

You can use TOP WITH TIES and RANK():

SELECT TOP (1) WITH TIES [JOB ROLE], [CITY], COUNT(DISTINCT [EMPLOYEE_ID]) as [COUNT]
FROM MyTable
GROUP BY [JOB ROLE], [CITY]
ORDER BY RANK() OVER (ORDER BY COUNT(DISTINCT [EMPLOYEE_ID]) DESC;

Or, use a subquery and RANK():

SELECT [JOB ROLE], [CITY], [COUNT]
FROM (SELECT [JOB ROLE], [CITY],
             COUNT(DISTINCT [EMPLOYEE_ID]) as [COUNT],
             RANK() OVER (ORDER BY COUNT(DISTINCT [EMPLOYEE_ID]) DESC) as seqnum
      FROM MyTable
      GROUP BY [JOB ROLE], [CITY]
     ) jc
WHERE seqnum = 1;

EDIT:

The question seems to have changed to be the most common job role per city. The above queries are easily modified:

SELECT TOP (1) WITH TIES [JOB ROLE], [CITY], COUNT(DISTINCT [EMPLOYEE_ID]) as [COUNT]
FROM MyTable
GROUP BY [JOB ROLE], [CITY]
ORDER BY RANK() OVER (PARTITION BY [JOB ROLE] ORDER BY COUNT(DISTINCT [EMPLOYEE_ID]) DESC;

Or, use a subquery and RANK():

SELECT [JOB ROLE], [CITY], [COUNT]
FROM (SELECT [JOB ROLE], [CITY],
             COUNT(DISTINCT [EMPLOYEE_ID]) as [COUNT],
             RANK() OVER (PARTITION BY [JOB ROLE], ORDER BY COUNT(DISTINCT [EMPLOYEE_ID]) DESC) as seqnum
      FROM MyTable
      GROUP BY [JOB ROLE], [CITY]
     ) jc
WHERE seqnum = 1;
user_11440350

If you don't care about ties, you can do it with FIRST_VALUE() window function:

SELECT DISTINCT [JOB ROLE], 
       FIRST_VALUE([CITY]) OVER (PARTITION BY [JOB ROLE] ORDER BY COUNT(DISTINCT([EMPLOYEE_ID])) DESC)
FROM MyTable
GROUP BY [JOB ROLE], [CITY]
user_104988280
Related