Combine count and max in postgresql sql

Question

I have a problem to formulate an sql question in postgresql, hoping to get some help here

I have a table called visitor that contains an column called fk_employee_id, fk_employee_id contains different number between 1-10, example:

1,3,4,6,4,6,7,3,2,1,6,7,6

Now I want to find out which value that is the most frequent in this column (in this case 6) I have made an question that seem to solve my question;

SELECT fk_employee_id
FROM visitor
GROUP BY fk_employee_id
ORDER BY COUNT(fk_employee_id) DESC
LIMIT 1

but this question, doesn't get right if it is two values that are the most frequent one. So instead I try to write a question which contains max function but cant figure out how, anyone now how to do this?

@StuartLC This is the question, namely how to return more than one fk_employee_id should two or more be tied for having the highest count. — Tim Biegeleisen
– Tim Biegeleisen, Commented Jun 13, 2018 at 6:34
@StuartLC Tell me about it; sadly this is the case with the majority of questions I see these days :~( — Tim Biegeleisen
– Tim Biegeleisen, Commented Jun 13, 2018 at 6:39
Hi, yes if there are two values that are most frequent i want that to return, example value 5 and 6 are the most frequent. — Michael
– Michael, Commented Jun 13, 2018 at 7:21

Tim Biegeleisen · Accepted Answer · 2018-06-13 06:30:54Z

3

We can use RANK here to slightly modify your current query:

WITH cte AS (
    SELECT
        fk_employee_id,
        RANK() OVER (ORDER BY COUNT(*) DESC) rank
    FROM visitor
    GROUP BY fk_employee_id
)

SELECT fk_employee_id
FROM cte
WHERE rank = 1;

Demo

answered Jun 13, 2018 at 6:30

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

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

1 Comment

Michael Over a year ago

Thx Tim, that solved my question, and now i learned a new function rank:-)

Collectives™ on Stack Overflow

Combine count and max in postgresql sql

1 Answer 1

Demo

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related