Find the Most Frequent Value (Statistical Mode)

Identify the value that appears most often in a column using `GROUP BY` with `ORDER BY COUNT(*) DESC LIMIT 1`, or with `MODE() WITHIN GROUP` in PostgreSQL.

Created May 5, 2026 Last updated May 5, 2026 0/5 supported engines validation-green 1 example 2 scenarios

Docker-validated Not currently validation-green

Example 1

Find the most frequently given product rating

Rating 4 appears three times (more than any other), so it is the mode. The GROUP BY rating ORDER BY COUNT(*) DESC LIMIT 1 pattern groups rows by value, counts how many times each value appears, sorts highest-count first, and returns just the top row. PostgreSQL's MODE() WITHIN GROUP (ORDER BY rating) computes the same result directly as an aggregate, resolving ties by returning the smallest value in sort order. For this dataset there is a single clear winner so no tie-breaking is needed.

MySQL MariaDB SQLite

Engine-specific syntax

Source table data

Setup

CREATE TABLE product_ratings (rating INT);

INSERT INTO
  product_ratings
VALUES
  (3),
  (4),
  (4),
  (4),
  (5),
  (5),
  (2);

Validated query

SQL

SELECT
  rating AS mode_value
FROM
  product_ratings
GROUP BY
  rating
ORDER BY
  COUNT(*) DESC
LIMIT
  1;

Expected result

mode_value
4

SQL Server

Engine-specific syntax

Source table data

Setup

CREATE TABLE product_ratings (rating INT);

INSERT INTO
  product_ratings
VALUES
  (3),
  (4),
  (4),
  (4),
  (5),
  (5),
  (2);

Validated query

SQL

SELECT
  TOP 1 rating AS mode_value
FROM
  product_ratings
GROUP BY
  rating
ORDER BY
  COUNT(*) DESC;

Expected result

mode_value
4

PostgreSQL

Engine-specific syntax

Source table data

Setup

CREATE TABLE product_ratings (rating INT);

INSERT INTO
  product_ratings
VALUES
  (3),
  (4),
  (4),
  (4),
  (5),
  (5),
  (2);

Validated query

SQL

SELECT
  MODE () WITHIN GROUP (
    ORDER BY
      rating
  ) AS mode_value
FROM
  product_ratings;

Expected result

mode_value
4

PostgreSQL uses the MODE() ordered-set aggregate. SQL Server uses TOP 1 instead of LIMIT 1. MySQL, MariaDB, and SQLite use LIMIT 1.

Useful when

Where this command helps.

finding the most common product rating to summarise customer sentiment without outlier distortion
identifying the most frequently occurring error code in an application log to prioritise fixes

Explanation

What the command is doing.

The statistical mode is the value that occurs most frequently in a dataset. Unlike AVG or MEDIAN, it is the only measure of central tendency that applies to categorical (non-numeric) data and is unaffected by outliers. PostgreSQL 9.4+ offers MODE() WITHIN GROUP (ORDER BY column) as an ordered-set aggregate that returns the most frequent value directly, breaking ties by returning the smallest value in the defined order. All other supported engines achieve the same result by grouping on the column, counting occurrences, sorting descending, and keeping only the top row. MySQL, MariaDB, SQLite, and PostgreSQL use LIMIT 1; SQL Server uses TOP 1. When two or more values share the highest frequency (a multimodal distribution), this approach returns only one of them — add HAVING COUNT(*) = (SELECT MAX(c) FROM (...) t) to find all modes, or use PostgreSQL's MODE() aggregate which deterministically picks the lowest value in sort order.

Find the Most Frequent Value (Statistical Mode)

Find the most frequently given product rating

Where this command helps.

What the command is doing.

Keep moving through nearby tasks.