Ranking: ROW_NUMBER, RANK, DENSE_RANK

Ranking functions answer "first, second, third" without collapsing your rows. The trick is that ORDER BY inside OVER controls the ranking calculation, while the final ORDER BY controls how the grid is displayed.

13.1 gave you OVER and PARTITION BY: annotate, don't collapse. Ranking adds the second dial: the window order is the ORDER BY inside OVER, and it tells a window function what "first" means.

The two ORDER BY clauses do different jobs

Put the window order inside OVER. Put the display order at the end of the query. This campus example ranks days by revenue, but displays the final grid by date:

SELECT ds.day, ds.branch, ds.revenue,
       row_number() OVER (
         PARTITION BY ds.branch
         ORDER BY ds.revenue DESC
       ) AS revenue_position
FROM daily_sales AS ds
WHERE ds.branch = 'campus'
ORDER BY ds.day;

    day     | branch | revenue | revenue_position
------------+--------+---------+------------------
 2026-03-02 | campus |  445.00 |                7
 2026-03-03 | campus |  460.00 |                6
 2026-03-04 | campus |  505.00 |                4
 2026-03-05 | campus |  470.75 |                5
 2026-03-06 | campus |  520.00 |                3
 2026-03-07 | campus |  610.00 |                1
 2026-03-08 | campus |  595.00 |                2
(7 rows)

Read the two orders separately. The window says "within campus, highest revenue gets position 1." The final clause says "show the rows by day." That's why the positions jump around in the grid. The calculation and the display are not the same step.

ROW_NUMBER() is a ranking function: it writes a position number beside each row, based on the window order. It never collapses rows; it annotates them.

Ties make three different answers

A tie means two rows are equal according to the window's ORDER BY. Here is a tiny shift-score table where two baristas tie for first and two tie for the next score:

SELECT ss.barista, ss.drinks_made,
       row_number() OVER (ORDER BY ss.drinks_made DESC, ss.barista) AS row_number,
       rank() OVER (ORDER BY ss.drinks_made DESC) AS rank,
       dense_rank() OVER (ORDER BY ss.drinks_made DESC) AS dense_rank
FROM shift_scores AS ss
ORDER BY ss.drinks_made DESC, ss.barista;

 barista | drinks_made | row_number | rank | dense_rank
---------+-------------+------------+------+------------
 Ada     |          42 |          1 |    1 |          1
 Tomas   |          42 |          2 |    1 |          1
 Mina    |          38 |          3 |    3 |          2
 Priya   |          38 |          4 |    3 |          2
 Leo     |          35 |          5 |    5 |          3
(5 rows)

Three functions, three meanings:

• ROW_NUMBER() assigns a unique number to every row. Use it when you need exactly one first row, one second row, and so on.
• RANK() gives tied rows the same rank, then leaves a gap. Ada and Tomas share 1, so Mina and Priya get 3.
• DENSE_RANK() gives tied rows the same rank without gaps. Ada and Tomas share 1, so the next distinct score gets 2.

NTILE: quick buckets

NTILE(n) is a ranking helper that splits ordered rows into n numbered buckets. A quartile is one of four buckets; a decile is one of ten.

SELECT ds.day, ds.branch, ds.revenue,
       ntile(4) OVER (ORDER BY ds.revenue DESC) AS revenue_quartile
FROM daily_sales AS ds
WHERE ds.branch = 'center'
ORDER BY ds.revenue DESC;

    day     | branch | revenue | revenue_quartile
------------+--------+---------+------------------
 2026-03-07 | center | 1215.25 |                1
 2026-03-08 | center | 1180.00 |                1
 2026-03-06 | center | 1040.00 |                2
 2026-03-05 | center |  915.00 |                2
 2026-03-03 | center |  905.50 |                3
 2026-03-02 | center |  830.00 |                3
 2026-03-04 | center |  760.00 |                4
(7 rows)

This is useful for quick bands: top quartile customers, bottom decile delivery days, middle buckets for a dashboard. It is not the center of ranking work, but it is handy when the question asks for buckets instead of exact places.

The second-highest hourly rate, three ways

The interview classic sounds simple: find the second-highest salary. In the café data, the comparable question is the second-highest hourly rate. The word "second" hides a tie decision, so make that decision visible.

If you only need the rate, ask for distinct rates and take the second one:

SELECT DISTINCT s.hourly_rate
FROM staff AS s
ORDER BY s.hourly_rate DESC
LIMIT 1 OFFSET 1;

 hourly_rate
-------------
       22.00
(1 row)

That returns a value, not the staff row. To get the people at that rate, match the table back to the second distinct rate:

SELECT s.name, s.hourly_rate
FROM staff AS s
WHERE s.hourly_rate = (
  SELECT DISTINCT s2.hourly_rate
  FROM staff AS s2
  ORDER BY s2.hourly_rate DESC
  LIMIT 1 OFFSET 1
)
ORDER BY s.name;

 name | hourly_rate
------+-------------
 Yuki |       22.00
(1 row)

The window version keeps the ranking rule beside each row, then filters one step later:

SELECT ranked.name, ranked.hourly_rate
FROM (
  SELECT s.name, s.hourly_rate,
         dense_rank() OVER (ORDER BY s.hourly_rate DESC) AS pay_rank
  FROM staff AS s
) AS ranked
WHERE ranked.pay_rank = 2
ORDER BY ranked.name;

 name | hourly_rate
------+-------------
 Yuki |       22.00
(1 row)

The tradeoff is plain. DISTINCT plus LIMIT/OFFSET is short when you only need the value. The match-back query gets the rows too, but repeats the value-finding logic inside a subquery. DENSE_RANK() says the tie rule in the same row shape you want to keep, which becomes more valuable when you rank inside each department, branch, or day. That "compute first, filter one step later" shape is the pattern lesson 13.4 turns into top-N and dedup work.

You now have the three ranking tools and the second ORDER BY dial. Next, LAG, LEAD, and frames let a row look at its neighbors and at a moving slice of the window.