Note 485

Christian Kruse

TIL: there is an aggregate function mode() WITHIN GROUP (ORDER BY sort_expression) in PostgreSQL. It returns the most frequent value of a group. So, for example when you have this table:

CREATE TABLE foo (id serial, user_id integer, task_id integer);

You can get the most frequent task ID for each user ID by using this aggregate function:

SELECT user_id, mode() WITHIN GROUP (ORDER BY task_id)
FROM foo
GROUP BY user_id;