Find multiple maximum values form a table

Question

I have read answers to similar questions but I cannot find a solution to my particular problem.

I will use a simple example to demonstrate my question.

I have a table called 'Prizes' with two columns: Employees and Awards

The employee column lists the employee's ID and award shows a single award won by the employee. If an employee has won multiple awards their ID will be listed in multiple rows of the table along with each unique award.

The table would look as follows:

Employee      AWARD
    1     Best dressed
    1     Most attractive
    2     Biggest time waster
    1     Most talkative
    3     Hardest worker
    4     Most shady
    3     Most positive
    3     Heaviest drinker
    2     Most facebook friends

Using this table, how would I select the ID's of the employees who won the most awards?

The output should be:

Employee
   1
   3

For the example as both these employees won 3 awards

Currently, the query below outputs the employee ID along with the number of awards they have won in descending order:

SELECT employee,COUNT(*) AS num_awards
FROM prizes
GROUP BY employee
ORDER BY num_awards DESC;

Would output:

employee  num_awards
  1       3
  3       3
  2       2
  4       1

How could I change my query to select the employee(s) with the most awards?

Gordon Linoff · Accepted Answer · 2015-12-08 15:17:46Z

3

A simple way to express this is using rank() or dense_rank():

SELECT p.*
FROM (SELECT employee, COUNT(*) AS num_awards,
             RANK() OVER (ORDER BY COUNT(*) DESC) as seqnum
      FROM prizes
      GROUP BY employee
     ) p
WHERE seqnum = 1;

Being able to combine aggregation functions and analytic functions can make these queries much more concise.

answered Dec 8, 2015 at 15:17

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Shiftz Over a year ago

This works perfect thanks. Would I be correct in assuming that I could also remove 'COUNT(*) AS num_awards' from the select statement as it's not really useful? EDIT: In my actual query I only need the employee's ID with the most awards and not how many they actually won.

Gordon Linoff Over a year ago

@Shiftz . . . You can remove the count(*) as num_awards. Also, just select employee.

Vamsi Prabhala · Accepted Answer · 2015-12-08 15:12:01Z

3

You can use dense_rank to get all the rows with highest counts.

with cnts as (
SELECT employee, count(*) cnt
FROM prizes
GROUP BY employee)
, ranks as (select employee, cnt, dense_rank() over(order by cnt desc) rnk 
            from cnts)
select employee, cnt 
from ranks where rnk = 1

answered Dec 8, 2015 at 15:12

Vamsi Prabhala

49.4k4 gold badges41 silver badges64 bronze badges

Collectives™ on Stack Overflow

Find multiple maximum values form a table

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related