Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am using Postgresql 9.0. I have the following fields in a table: id, name.

 id    name 
 1     John
 1     Mary
 1     Mary
 1     Mary
 1     John
 1     Mary
 3     Paul
 3     Paul
 3     George
 .     .
 .     .

For each id, I want to select the name that occurs the most. How can I do that?

I tried with the following query but it doesn't work:

select id, max(name) 
from table 
group by id;
share|improve this question
1  
max will find the name that sorts last lexically, not the name that accurs the most times. You really want a mode (in the statistical sense) aggregate for that, and I don't think one is offered. Someone's written one on the wiki, but I doubt it's very efficient. Try it: wiki.postgresql.org/wiki/Aggregate_Mode and scottrbailey.wordpress.com/2009/05/22/… –  Craig Ringer Dec 15 '12 at 8:38

1 Answer 1

This isn't trivial. First, you want group by id and name and count the rows:

SELECT COUNT(*)
...
GROUP BY id, name

Then select the maximum count for every id. One way to achieve this is by window functions. The RANK() function:

RANK() OVER (PARTITION BY id ORDER BY COUNT(*) DESC)

assigns a number to every row of the result (after the grouping is done), arranging them (the rows) in partitions with the same id and ordered by COUNT(*) DESC, so for every (partition of) id, the row(s) with the maximum count are assigned a rank of 1. Thus we need to put the above in a derived table and use a WHERE condition to keep only these rows:

WHERE rnk = 1

The final query is like this:

SELECT
    id, name, cnt
FROM
    ( SELECT id, name, COUNT(*) AS cnt,
             RANK() OVER (PARTITION BY id ORDER BY COUNT(*) DESC) AS rnk
      FROM tableX
      GROUP BY id, name
    ) AS tg 
WHERE
    rnk = 1 ;

Tested at SQL-Fiddle


Note that if you have ties in the first place (two or more names with the same maximum count), all these will be returned. If you want strictly one row per id in the final results, you have to use the ROW_NUMBER() instead of the RANK() and possibly alter the ORDER BY clause to explicitly select how the ties will be resolved:

ROW_NUMBER() OVER (PARTITION BY id ORDER BY COUNT(*) DESC, name ASC) AS rnk

Tested: SQL-Fiddle test-2.

share|improve this answer
    
Thanks a lot, that was of great help! –  Tudor Dec 15 '12 at 8:46

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.