Introduction

Finding the most frequent value in a SQL column is a classic aggregation problem. In statistics this is the mode. In SQL, the standard solution is to group rows by the target column, count each group, and then return the group with the highest count.

The Basic Query Pattern

For most databases, the simplest solution looks like this:

1SELECT status, COUNT(*) AS frequency
2FROM orders
3GROUP BY status
4ORDER BY frequency DESC
5LIMIT 1;

This query does three things:

• 'GROUP BY status creates one group per distinct value'
• 'COUNT(*) measures how many rows are in each group'
• 'ORDER BY frequency DESC places the largest count first'

LIMIT 1 works in PostgreSQL, MySQL, and SQLite. In SQL Server you would normally use TOP 1 instead.

Example with Ties

Sometimes there is more than one most frequent value. If two values both occur ten times, LIMIT 1 returns only one of them, often based on arbitrary ordering.

To return every tied mode, use a window function.

1WITH counts AS (
2    SELECT status, COUNT(*) AS frequency
3    FROM orders
4    GROUP BY status
5), ranked AS (
6    SELECT status,
7           frequency,
8           DENSE_RANK() OVER (ORDER BY frequency DESC) AS frequency_rank
9    FROM counts
10)
11SELECT status, frequency
12FROM ranked
13WHERE frequency_rank = 1;

This returns every value whose count matches the maximum count.

Handling NULL Values

Be explicit about whether NULL should count as a value. COUNT(*) counts rows, including rows where the grouped column is NULL. COUNT(column_name) ignores NULL values.

1SELECT category, COUNT(*) AS frequency
2FROM products
3GROUP BY category
4ORDER BY frequency DESC
5LIMIT 1;

If category can be NULL, this query may report NULL as the most frequent result. If that is not what you want, filter it out.

1SELECT category, COUNT(*) AS frequency
2FROM products
3WHERE category IS NOT NULL
4GROUP BY category
5ORDER BY frequency DESC
6LIMIT 1;

Returning the Full Row, Not Just the Value

Sometimes you need the whole record associated with the most frequent value. In that case, first compute the mode, then join back to the base table.

1WITH most_common AS (
2    SELECT customer_id
3    FROM orders
4    GROUP BY customer_id
5    ORDER BY COUNT(*) DESC
6    LIMIT 1
7)
8SELECT o.*
9FROM orders AS o
10JOIN most_common AS mc
11  ON o.customer_id = mc.customer_id;

Be careful here: if the most frequent value occurs many times, this returns many rows. That is usually correct, but it surprises people who expected only one row.

Performance Considerations

This query pattern is straightforward, but on a very large table it can still be expensive. Grouping requires scanning relevant rows and building aggregates.

An index on the grouped column can help, especially if the database can use an index-only plan.

CREATE INDEX idx_orders_status ON orders(status);

Whether the index helps depends on the database engine, data distribution, and query plan. For very large analytical workloads, a materialized summary table may be better than recomputing the mode repeatedly.

Database-Specific Variants

Different systems vary slightly:

• PostgreSQL, MySQL, and SQLite use LIMIT 1
• SQL Server uses TOP 1
• Oracle often uses FETCH FIRST 1 ROW ONLY

The aggregation logic is the same even though the final row-limiting syntax changes.

Common Pitfalls

A common mistake is forgetting about ties and assuming the top row is the only correct answer. If ties matter, use DENSE_RANK() or compare against the maximum count.

Another mistake is counting non-NULL values with COUNT(column) when you really wanted row counts. That changes the result whenever the column contains NULL.

Finally, do not try to select non-aggregated columns alongside the grouped value unless they are included in the GROUP BY or wrapped in an aggregate. SQL engines either reject that query or return undefined-looking results depending on the dialect.

Summary

• The standard solution is GROUP BY, COUNT(*), and descending sort.
• Use a window function when ties must be returned explicitly.
• Decide whether NULL should count as a real candidate value.
• Join back to the base table only if you need full rows associated with the most frequent value.
• On large tables, indexes or pre-aggregated summaries can improve performance.