Can I do a max(count(*)) in SQL?

sql

window-functions

sql-performance

sql-optimization

byNikita Barsukov·Jan 31, 2025

Get the maximum count across groups in SQL by nesting a query to count records per group, and then selecting the highest value:

-- Nesting a count query to get max count
SELECT MAX(GroupCount) as MaxCount
FROM (
    SELECT COUNT(*) as GroupCount 
    FROM YourTable 
    GROUP BY YourColumn) SubQuery

Or, sort counts in descending order and fetch the first row - easy peasy:

-- Good old descending sorting
SELECT COUNT(*) as GroupCount
FROM YourTable
GROUP BY YourColumn
ORDER BY GroupCount DESC
LIMIT 1

Both pieces of code will yield the largest group size in YourTable grouped by YourColumn.

No Ordinary SQL: Advanced Techniques

For moments when the stakes are high and MAX(COUNT(*)) just won't cut it, here are your secret weapons:

Window Functions: The High Rise View

Window functions like RANK() let you look at the big picture:

-- Faking a beauty contest for movies 
SELECT Year, COUNT(*) as MovieCount,
RANK() OVER (ORDER BY COUNT(*) DESC) as RankIndex
FROM Movies
GROUP BY Year;

WITH TIES: Equality Matters

In a balanced universe, all equal counts get a medal:

-- Celebrating democracy with WITH TIES
SELECT TOP 1 WITH TIES Year, COUNT(*) as MovieCount
FROM Movies
GROUP BY Year
ORDER BY COUNT(*) DESC;

HAVING Clause: The Gatekeeper

Use HAVING to keep the bullies (groups that don't satisfy conditions) out of the playground:

-- Only cool kids allowed beyond this point
SELECT Year, COUNT(*) as MovieCount
FROM Movies
GROUP BY Year
HAVING COUNT(*) = 
  (SELECT MAX(MovieCount) FROM 
     (SELECT Year, COUNT(*) as MovieCount 
      FROM Movies 
      GROUP BY Year) SubQuery);

DISTINCT ON in PostgreSQL: Because Uniqueness Counts

When in PostgreSQL, DISTINCT ON is your friend in finding the top count:

-- DISTINCT ON: Making every count matter!
SELECT DISTINCT ON (Year) Year, COUNT(*) as MovieCount
FROM Movies 
JOIN Casting ON Movies.ID = Casting.MovieID
JOIN Actor ON Casting.ActorID = Actor.ID
WHERE Actor.Name = 'John Travolta'
GROUP BY Year
ORDER BY Year, MovieCount DESC;

Pitfalls and Performances: Dodging Landmines

SQL queries with MAX(COUNT(*)) can steal a lot of your computing resources. Here's how you outsmart them:

Streamline your queries. The shorter, the merrier.
Use indexing on fields used in JOIN, WHERE, and ORDER BY
Check the execution plan of your queries to identify performance bottlenecks.

Visualizing SQL: If SQL Was a Garden

Let's say a tree is a group of records, with COUNT(*) being the apple count. The MAX(COUNT(*)) gives us the tree with the most apples:

🌳(🍎🍎🍎🍎) 🌳(🍎🍎) 🌳(🍎🍎🍎) 

🏆🌳(🍎🍎🍎🍎)

Here's the SQL query for the species with the Golden Apple Award:

SELECT 
  Species, 
  COUNT(*) AS AppleCount
FROM 
  AppleTrees
GROUP BY 
  Species
ORDER BY 
  AppleCount DESC
LIMIT 1;

SQL in Action: Real World Scenarios

Let's give a stage to our SQL code and let them play, shall we?

Scenario 1: John Travolta wants to know his most industrious year in cinema. We deliver:

-- Helping John Travolta take a trip down memory lane
SELECT Year, COUNT(*) as MovieCount
FROM Movies 
JOIN Casting ON Movies.ID = Casting.MovieID
JOIN Actor ON Casting.ActorID = Actor.ID
WHERE Actor.Name = 'John Travolta'
GROUP BY Year
ORDER BY MovieCount DESC
LIMIT 1;

Scenario 2: Movie buffs want to know the year with maximum movie count. We've got that covered:

-- Giving movie nerds something to think about
WITH YearlyCount AS (
  SELECT Year, COUNT(*) as MovieCount
  FROM Movies
  GROUP BY Year
)
SELECT Year, MovieCount
FROM YearlyCount
WHERE MovieCount = (SELECT MAX(MovieCount) FROM YearlyCount);