MODE() WITHIN GROUP (ORDER BY col) returns the most frequent value in a column — the statistical mode — in one tidy expression, with no subqueries or window functions. It is an ordered-set aggregate, and the ORDER BY decides which value wins when frequencies tie.
Basic syntax
The mode is the value that occurs most often. In PostgreSQL it is written as an ordered-set aggregate: both WITHIN GROUP and ORDER BY are required.
SELECT MODE() WITHIN GROUP (ORDER BY status) AS top_status
FROM orders;
SELECT MODE() WITHIN GROUP (ORDER BY country) AS top_country
FROM users;
Worth keeping in mind:
- The
ORDER BY here is not about sorting the result; it names the column the mode is computed over and supplies the tie-break rule.
NULL is ignored when counting frequencies, just like in ordinary aggregates.- It works with any sortable type: text, numbers, dates, enums.
Mode per group
Most of the time you want the mode by slice, not globally: the most frequent status for each user, the most popular department per manager. Add GROUP BY.
SELECT user_id,
MODE() WITHIN GROUP (ORDER BY status) AS usual_status
FROM orders
GROUP BY user_id;
SELECT manager_id,
MODE() WITHIN GROUP (ORDER BY dept) AS main_dept
FROM employees
GROUP BY manager_id;
The expression reads as a single column in the SELECT, so other aggregates — COUNT(*), SUM(amount), and so on — sit happily next to it with no extra joins.
Tie-breaking
The key detail: if several values share the top frequency, MODE() returns the one that comes first in the given ORDER BY. That makes the result deterministic — unlike a LIMIT 1 without an explicit ordering.
SELECT MODE() WITHIN GROUP (ORDER BY status) AS top_status
FROM orders;
SELECT MODE() WITHIN GROUP (ORDER BY amount DESC) AS top_amount
FROM orders;
Gotcha: DESC in the ORDER BY does not mean "the least frequent value." The mode is still chosen by maximum frequency; DESC only changes which of the equally frequent values wins the tie. Finding a rare value needs a completely different query with COUNT(*) sorted ascending.
Versus GROUP BY + COUNT + LIMIT 1
The classic way to find a mode is to count frequencies and take the top row:
SELECT status, COUNT(*) AS cnt
FROM orders
GROUP BY status
ORDER BY cnt DESC
LIMIT 1;
It works, but it has weak spots compared to MODE():
- On a tie,
LIMIT 1 grabs an arbitrary row unless you add a second sort key — the result is non-deterministic.
- For a per-group mode you must reach for a window function (
ROW_NUMBER() OVER (PARTITION BY ...)) or a correlated subquery — noticeably more code.
- You cannot place other aggregates of the same grouping next to it in one simple
SELECT.
The same "mode per user" via a window looks like this:
SELECT user_id, status AS usual_status
FROM (
SELECT user_id, status, COUNT(*) AS cnt,
ROW_NUMBER() OVER (PARTITION BY user_id
ORDER BY COUNT(*) DESC, status) AS rn
FROM orders
GROUP BY user_id, status
) t
WHERE rn = 1;
MODE() WITHIN GROUP (ORDER BY status) with GROUP BY user_id does the same thing in one line.
Differences across engines
MODE() as an ordered-set aggregate is standard SQL, supported by PostgreSQL, Oracle, and DB2. But several popular engines lack it:
- MySQL and SQLite: no
MODE() function. Use GROUP BY ... ORDER BY COUNT(*) DESC LIMIT 1 or the window variant above.
- ClickHouse: it ships separate functions —
topK(1)(col) returns an array with the most frequent value, and anyHeavy(col) gives an approximate mode over large streams.
SELECT topK(1)(status) AS top_status FROM orders;
Bottom line: when your database supports MODE() WITHIN GROUP, it is the shortest, most readable, and most deterministic way to get the most frequent value — especially with GROUP BY, where the alternative balloons into a window-function subquery.
MODE() WITHIN GROUP (ORDER BY col)returns the most frequent value in a column — the statistical mode — in one tidy expression, with no subqueries or window functions. It is an ordered-set aggregate, and theORDER BYdecides which value wins when frequencies tie.Basic syntax
The mode is the value that occurs most often. In PostgreSQL it is written as an ordered-set aggregate: both
WITHIN GROUPandORDER BYare required.-- Most frequent order status across the whole table SELECT MODE() WITHIN GROUP (ORDER BY status) AS top_status FROM orders; -- Most common country among registered users SELECT MODE() WITHIN GROUP (ORDER BY country) AS top_country FROM users;Worth keeping in mind:
ORDER BYhere is not about sorting the result; it names the column the mode is computed over and supplies the tie-break rule.NULLis ignored when counting frequencies, just like in ordinary aggregates.Mode per group
Most of the time you want the mode by slice, not globally: the most frequent status for each user, the most popular department per manager. Add
GROUP BY.-- Most frequent status per user SELECT user_id, MODE() WITHIN GROUP (ORDER BY status) AS usual_status FROM orders GROUP BY user_id; -- Dominant department per manager SELECT manager_id, MODE() WITHIN GROUP (ORDER BY dept) AS main_dept FROM employees GROUP BY manager_id;The expression reads as a single column in the
SELECT, so other aggregates —COUNT(*),SUM(amount), and so on — sit happily next to it with no extra joins.Tie-breaking
The key detail: if several values share the top frequency,
MODE()returns the one that comes first in the givenORDER BY. That makes the result deterministic — unlike aLIMIT 1without an explicit ordering.-- On a tie, the alphabetically smallest status wins SELECT MODE() WITHIN GROUP (ORDER BY status) AS top_status FROM orders; -- Force the tie to resolve toward the largest amount instead SELECT MODE() WITHIN GROUP (ORDER BY amount DESC) AS top_amount FROM orders;Gotcha:
DESCin theORDER BYdoes not mean "the least frequent value." The mode is still chosen by maximum frequency;DESConly changes which of the equally frequent values wins the tie. Finding a rare value needs a completely different query withCOUNT(*)sorted ascending.Versus GROUP BY + COUNT + LIMIT 1
The classic way to find a mode is to count frequencies and take the top row:
-- Old-school: count, sort, take the top row SELECT status, COUNT(*) AS cnt FROM orders GROUP BY status ORDER BY cnt DESC LIMIT 1;It works, but it has weak spots compared to
MODE():LIMIT 1grabs an arbitrary row unless you add a second sort key — the result is non-deterministic.ROW_NUMBER() OVER (PARTITION BY ...)) or a correlated subquery — noticeably more code.SELECT.The same "mode per user" via a window looks like this:
-- Per-group mode the hard way SELECT user_id, status AS usual_status FROM ( SELECT user_id, status, COUNT(*) AS cnt, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY COUNT(*) DESC, status) AS rn FROM orders GROUP BY user_id, status ) t WHERE rn = 1;MODE() WITHIN GROUP (ORDER BY status)withGROUP BY user_iddoes the same thing in one line.Differences across engines
MODE()as an ordered-set aggregate is standard SQL, supported by PostgreSQL, Oracle, and DB2. But several popular engines lack it:MODE()function. UseGROUP BY ... ORDER BY COUNT(*) DESC LIMIT 1or the window variant above.topK(1)(col)returns an array with the most frequent value, andanyHeavy(col)gives an approximate mode over large streams.-- ClickHouse: most frequent value as a 1-element array SELECT topK(1)(status) AS top_status FROM orders;Bottom line: when your database supports
MODE() WITHIN GROUP, it is the shortest, most readable, and most deterministic way to get the most frequent value — especially withGROUP BY, where the alternative balloons into a window-function subquery.