SQL · Guides · October 30, 2026 · 13 min read

Top N per group in SQL: ROW_NUMBER, RANK, QUALIFY and LATERAL

To get the top N rows per group in SQL, number the rows inside each group with a window function and keep the first N: ROW_NUMBER() OVER (PARTITION BY group_col ORDER BY value DESC) in a subquery or CTE, then WHERE rn <= N outside it. PARTITION BY restarts the numbering for each group, and ORDER BY decides what “top” means. The real decisions are about ties: ROW_NUMBER always returns exactly N rows per group, RANK and DENSE_RANK return more when values are equal. The same tools find the second or Nth highest value.

All examples run on the airline database from the Nomto SQL course (104 airports in 17 time zones, 33,121 flights, about a million ticket segments), in PostgreSQL syntax on DuckDB, the engine of the Nomto SQL room. Every result table is the real output. For how window functions work in general, see SQL window functions.

Top N per group with ROW_NUMBER

The three busiest airports by departures in each time zone. First count departures per airport, then number the airports inside each time zone, then filter:

WITH d AS (
  SELECT ap.timezone, f.departure_airport, COUNT(*) AS departures
  FROM flights f
  JOIN airports ap ON ap.airport_code = f.departure_airport
  GROUP BY ap.timezone, f.departure_airport
), ranked AS (
  SELECT timezone, departure_airport, departures,
         ROW_NUMBER() OVER (
           PARTITION BY timezone
           ORDER BY departures DESC, departure_airport
         ) AS rn
  FROM d
)
SELECT timezone, departure_airport, departures, rn
FROM ranked
WHERE rn <= 3
  AND timezone IN ('Europe/Moscow', 'Asia/Yekaterinburg', 'Asia/Krasnoyarsk')
ORDER BY timezone, rn;

(The time zone filter only shortens the output; without it you get up to three airports for each of the 17 zones.)

Three things in this query are not optional:

1. The filter goes outside. Window functions are computed after WHERE, so WHERE ROW_NUMBER() OVER (...) <= 3 is an error. Compute rn in a CTE or subquery and filter in the next step.
2. A tie-breaker in ORDER BY. departure_airport as the second sort key makes the numbering repeatable. Without it, rows with equal counts can swap places between runs.
3. Small groups just return fewer rows. Eight of the 17 zones have a single airport, so they contribute one row each, not three.

Note the grain: the window runs over the grouped result of d, one row per airport, not over individual flights. You can also put COUNT(*) straight into the window’s ORDER BY and skip the first CTE, as in the window functions guide.

Ties: ROW_NUMBER vs RANK vs DENSE_RANK

With real data, ties are common. Destinations from Kazan ranked by number of flights:

SELECT departure_airport, arrival_airport, COUNT(*) AS flights,
       ROW_NUMBER() OVER w AS row_num,
       RANK()       OVER w AS rnk,
       DENSE_RANK() OVER w AS dense_rnk
FROM flights
WHERE departure_airport = 'KZN'
GROUP BY departure_airport, arrival_airport
WINDOW w AS (PARTITION BY departure_airport ORDER BY COUNT(*) DESC)
ORDER BY flights DESC, arrival_airport;

Five destinations share second place with 61 flights each. ROW_NUMBER hands out 2 to 6 among them in no meaningful order (there is no tie-breaker here on purpose, and another run may number them differently). The WINDOW w AS (...) clause defines the window once and reuses it; PostgreSQL, DuckDB and MySQL 8 support it, SQL Server since 2022.

Now ask for “the top 2 destinations” and count how many rows each function returns, for Kazan and for Novosibirsk:

WITH r AS (
  SELECT departure_airport, arrival_airport, COUNT(*) AS flights,
         ROW_NUMBER() OVER w AS row_num,
         RANK()       OVER w AS rnk,
         DENSE_RANK() OVER w AS dense_rnk
  FROM flights
  WHERE departure_airport IN ('KZN', 'OVB')
  GROUP BY departure_airport, arrival_airport
  WINDOW w AS (PARTITION BY departure_airport ORDER BY COUNT(*) DESC)
)
SELECT departure_airport,
       COUNT(*) FILTER (WHERE row_num <= 2)   AS row_number_le_2,
       COUNT(*) FILTER (WHERE rnk <= 2)       AS rank_le_2,
       COUNT(*) FILTER (WHERE dense_rnk <= 2) AS dense_rank_le_2
FROM r
GROUP BY departure_airport
ORDER BY departure_airport;

The same “top 2” returns anything from 2 to 13 rows. From Novosibirsk, four destinations tie for first with 122 flights, so RANK() <= 2 returns those four (the next rank is 5). DENSE_RANK() <= 2 means “the two highest values”, 122 and 61, and nine destinations have 61 flights.

Which to choose:

• Exactly N rows per group (a dashboard widget with three slots, a sample): ROW_NUMBER with a tie-breaker that makes sense to the reader, such as alphabetical or most recent.
• Everyone who earned a top-N place, ties included (a leaderboard): RANK. The group can exceed N.
• All rows with the N highest values (the top 3 price points, however many tickets each): DENSE_RANK.

If you use ROW_NUMBER and ties are possible, say how they were broken. A list that silently drops four of five equal routes is misleading.

QUALIFY: filtering window functions directly

Several engines have a QUALIFY clause that filters on a window function without a subquery, the way HAVING filters on an aggregate:

WITH d AS (
  SELECT ap.timezone, f.departure_airport, COUNT(*) AS departures
  FROM flights f
  JOIN airports ap ON ap.airport_code = f.departure_airport
  GROUP BY ap.timezone, f.departure_airport
)
SELECT timezone, departure_airport, departures
FROM d
QUALIFY ROW_NUMBER() OVER (PARTITION BY timezone ORDER BY departures DESC, departure_airport) <= 3
ORDER BY timezone, departures DESC
LIMIT 5;

QUALIFY works in DuckDB, Snowflake, BigQuery, Databricks and Teradata. PostgreSQL, MySQL, SQL Server and Oracle do not have it, so for portable code keep the CTE version. (SQL Server and Oracle users sometimes reach for TOP n WITH TIES or FETCH FIRST n ROWS WITH TIES, but those apply to the whole result, not per group.)

Top 1 per group: DISTINCT ON

When N is 1, PostgreSQL and DuckDB offer DISTINCT ON, which keeps the first row of each group according to ORDER BY. The busiest airport in each time zone:

WITH d AS (
  SELECT ap.timezone, f.departure_airport, COUNT(*) AS departures
  FROM flights f
  JOIN airports ap ON ap.airport_code = f.departure_airport
  GROUP BY ap.timezone, f.departure_airport
)
SELECT DISTINCT ON (timezone) timezone, departure_airport, departures
FROM d
ORDER BY timezone, departures DESC
LIMIT 5;

ORDER BY must start with the DISTINCT ON columns, and the rest of it picks the winner. It is short and fast, but only returns one row per group and does not exist in MySQL or SQL Server. A tempting alternative, GROUP BY timezone with MAX(departures), gives the top value but not which airport it belongs to; joining back to find the airport returns several rows when two airports tie.

LATERAL: the top N for each row of another table

ROW_NUMBER ranks every row in every group and then throws most of them away. When the groups come from a small table and the rows from a big one, a LATERAL join is the alternative: for each row on the left, run a small ORDER BY ... LIMIT N subquery. The next three departures from each Moscow airport after noon on 20 August:

SELECT a.airport_code, nxt.flight_no, nxt.arrival_airport, nxt.scheduled_departure
FROM airports a
CROSS JOIN LATERAL (
  SELECT f.flight_no, f.arrival_airport, f.scheduled_departure
  FROM flights f
  WHERE f.departure_airport = a.airport_code
    AND f.scheduled_departure >= TIMESTAMP '2026-08-20 12:00'
  ORDER BY f.scheduled_departure, f.flight_no
  LIMIT 3
) AS nxt
WHERE a.city = 'Moscow'
ORDER BY a.airport_code, nxt.scheduled_departure;

LATERAL lets the subquery refer to a.airport_code from the outer table, which an ordinary subquery in FROM cannot. In PostgreSQL, with an index on (departure_airport, scheduled_departure), each subquery reads just three index entries, which beats ranking millions of rows when you need “the latest 5 orders for each of these 20 customers”. Use LEFT JOIN LATERAL (...) ON TRUE to keep left rows with no matches. SQL Server writes the same thing as CROSS APPLY / OUTER APPLY with TOP 3; MySQL supports LATERAL from 8.0.14.

The second highest value, and the Nth

“Find the second highest value” is the most common form of the question. On economy segment prices, three approaches give two different answers:

SELECT amount
FROM ticket_flights
WHERE fare_conditions = 'Economy'
ORDER BY amount DESC
LIMIT 1 OFFSET 1;

That is the top price again: 99 segments were sold at $745, and OFFSET 1 skips only one of them. Add DISTINCT and you get the second highest value:

SELECT DISTINCT amount
FROM ticket_flights
WHERE fare_conditions = 'Economy'
ORDER BY amount DESC
LIMIT 1 OFFSET 1;

DENSE_RANK gives the same answer and keeps the rows, so you can see how many segments sit at that price:

SELECT amount, COUNT(*) AS segments
FROM (
  SELECT amount, DENSE_RANK() OVER (ORDER BY amount DESC) AS dr
  FROM ticket_flights
  WHERE fare_conditions = 'Economy'
) r
WHERE dr = 2
GROUP BY amount;

For the Nth highest, change OFFSET 1 to OFFSET N-1, or dr = 2 to dr = N. The rule is the same as before: if “second” means the second distinct value, deduplicate (DISTINCT or DENSE_RANK); ROW_NUMBER and a plain OFFSET count rows, not values. SQL Server writes the offset as ORDER BY amount DESC OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY. Without any of these features, the old pattern is a correlated subquery: the value that has exactly N-1 distinct values above it, WHERE (SELECT COUNT(DISTINCT t2.amount) FROM t t2 WHERE t2.amount > t1.amount) = N - 1. It works everywhere but can be slow on large tables. For the version with MAX below MAX, see question 11 in SQL interview questions.

When there is no Nth value

Comfort class has only five distinct prices. Asking for the sixth returns an empty result, which a report or an interview grader may not expect. Wrapping the query in a scalar subquery turns “no rows” into a single NULL:

SELECT (
  SELECT DISTINCT amount
  FROM ticket_flights
  WHERE fare_conditions = 'Comfort'
  ORDER BY amount DESC
  LIMIT 1 OFFSET 5
) AS sixth_highest;

The Nth highest in each group

Combine both ideas: DENSE_RANK with PARTITION BY, filtered to = N. The second busiest airport in each time zone:

WITH d AS (
  SELECT ap.timezone, f.departure_airport, COUNT(*) AS departures
  FROM flights f
  JOIN airports ap ON ap.airport_code = f.departure_airport
  GROUP BY ap.timezone, f.departure_airport
), r AS (
  SELECT *, DENSE_RANK() OVER (PARTITION BY timezone ORDER BY departures DESC) AS dr
  FROM d
)
SELECT timezone, departure_airport AS second_busiest, departures
FROM r
WHERE dr = 2
ORDER BY departures DESC
LIMIT 5;

Only nine time zones appear in the full result: the eight with a single airport have no second place and drop out. If every group must be listed, start from the list of groups and LEFT JOIN the ranked rows with dr = 2 in the join condition.

Without window functions

Before window functions, top N per group was written with a correlated subquery: keep a row if fewer than N rows in the same group beat it.

WITH d AS (
  SELECT ap.timezone, f.departure_airport, COUNT(*) AS departures
  FROM flights f
  JOIN airports ap ON ap.airport_code = f.departure_airport
  GROUP BY ap.timezone, f.departure_airport
)
SELECT d1.timezone, d1.departure_airport, d1.departures
FROM d d1
WHERE (SELECT COUNT(*) FROM d d2
       WHERE d2.timezone = d1.timezone
         AND d2.departures > d1.departures) < 3
  AND d1.timezone IN ('Europe/Moscow', 'Asia/Yekaterinburg', 'Asia/Krasnoyarsk')
ORDER BY d1.timezone, d1.departures DESC;

It returns the same nine rows as the first query. It behaves like RANK on ties (all tied rows pass), and it compares every row with every other row in its group, so it slows down quickly as groups grow. You will still meet it in old code and in MySQL 5.7, which has no window functions. Anywhere else, prefer ROW_NUMBER.

Which method to use

FAQ

How do I select the top N rows per group in SQL?

Number rows in each group with ROW_NUMBER() OVER (PARTITION BY group_col ORDER BY value DESC) in a CTE or subquery, then filter WHERE rn <= N in the outer query. Add a tie-breaker column to the ORDER BY so the result is repeatable.

What is the greatest-n-per-group problem?

It is the general name for “for each group, return the row (or rows) with the highest value”, such as the latest order per customer or the top seller per region. It is hard with plain GROUP BY because MAX() returns the value but not the rest of its row. Window functions, DISTINCT ON and LATERAL are the standard solutions.

How do I find the second highest value in SQL?

SELECT DISTINCT value FROM t ORDER BY value DESC LIMIT 1 OFFSET 1, or DENSE_RANK() OVER (ORDER BY value DESC) = 2 in a subquery. Both skip duplicates of the top value. Without DISTINCT, OFFSET 1 may return the top value again.

Should I use ROW_NUMBER or RANK for top N?

ROW_NUMBER when you need exactly N rows per group; it breaks ties arbitrarily unless you add a tie-breaker. RANK when tied rows should share a place, which can return more than N rows. DENSE_RANK when you want every row with one of the N highest values.

Why can’t I use ROW_NUMBER in a WHERE clause?

WHERE is evaluated before window functions are computed, so the row number does not exist yet at that point. Compute it in a subquery or CTE and filter outside, or use QUALIFY in engines that support it.