Our “second-highest price” is the highest price

See the problem

The weekly price report has a line for the second-highest price in the store. Behind it is this query:

The report’s query

SELECT price AS second_price
FROM products
ORDER BY price DESC
LIMIT 1 OFFSET 1;

For months it said 45.00, the backpack. Then a speaker joins the electronics range at 59.00, the same price as the headphones. Nothing else changes. The next report says:

second_price
59.00

That’s the highest price, not the second. And in the summer sale, when every product costs 9.99, the report says the second-highest price is 9.99, though there’s only one price.

The query didn’t change. Why is it wrong now?

Define the task

Write the report’s query: the second-highest price in products, in one column called second_price.

• The second-highest different price. Two products at the top price are still one price.
• Always exactly one row. When there’s no second price (every product costs the same), that row holds NULL.

Pause and think

⏸ Pause and think

Sort the prices highest first, with the speaker in: 59.00, 59.00, 45.00, and so on. Which row does OFFSET 1 skip, and which does LIMIT 1 keep?

Give it two minutes with pen and paper before you open a hint.

Hints open one at a time:

Hint 1 of 3

OFFSET 1 skips one row, and LIMIT 1 keeps the next. With two 59.00 rows at the top, the row it keeps is the second 59.00.

Hint 2 of 3

SELECT DISTINCT price gives each different price once. Sort those, and the second row is the second price.

Hint 3 of 3

When every price is the same, DISTINCT leaves one row and OFFSET 1 skips it, so no row comes back. Put the query inside SELECT ( ... ) AS second_price: a subquery that finds no row gives NULL.

Understand the idea

DISTINCT keeps one row for each different row. Write it right after SELECT. It runs after SELECT has picked the columns, and before ORDER BY:

Runs	Clause	What it does
1	FROM products	Reads the table’s rows
2	WHERE ...	Keeps the rows where the condition is true
3	SELECT price	Picks the columns
4	DISTINCT	Drops a row when the same row is already kept
5	ORDER BY price DESC	Sorts what’s left
6	OFFSET 1, then LIMIT 1	Skips 1 row, then keeps 1

A row, not a column. DISTINCT compares every column you selected. SELECT DISTINCT category gives each category once. SELECT DISTINCT category, price gives each pair once, so a category with three prices shows up three times.

Same means exactly the same.

• Text compares letter by letter: 'Pune', 'Pune ' with a space, and 'pune' are three values. Practice problem 1 meets this.
• Numbers compare by value: 59 and 59.00 are the same.
• NULLs count as one value. NULL = NULL isn’t true, but DISTINCT puts all the NULLs together and keeps one. Practice problem 2 meets this.

ORDER BY can only use what you selected. SELECT DISTINCT customer_id FROM events ORDER BY occurred_at is an error. After DISTINCT merges a customer’s rows into one, that row has many times, so Postgres can’t tell which to sort by.

A subquery in SELECT always gives one value. SELECT ( SELECT ... ) AS second_price runs the inner query and puts its value in one row. If the inner query finds no row, the value is NULL. If it finds more than one, Postgres stops with an error.

Watch the report’s query run on some of the store’s products, with the speaker in. Then type your own, with prices that repeat, or all the same:

The second-highest price, step by step

⤢ Full view

1. FROM products
2. SELECT price
3. DISTINCT
4. ORDER BY price DESC
5. OFFSET 1
6. LIMIT 1
7. SELECT (…) AS second_price

products

name	price
Headphones	59.00
Backpack	45.00
Desk lamp	24.99
Wireless mouse	18.00
Speaker	59.00

Step 1 of 7. FROM products reads all 5 rows.

◀ BackPlayNext ▶Reset Ask me first Show as text

1SELECT (
2  SELECT DISTINCT price
3  FROM products
4  ORDER BY price DESC
5  LIMIT 1 OFFSET 1
6) AS second_price;

Try your own input

Products: name, priceHeadphones, 59.00 Backpack, 45.00 Desk lamp, 24.99 Wireless mouse, 18.00 Speaker, 59.00Show it

DISTINCT ON keeps the first row of each group. It’s Postgres only. SELECT DISTINCT ON (department_id) department_id, name, salary FROM employees ORDER BY department_id, salary DESC keeps one row for each department: the first one in ORDER BY order, which is the best-paid. ORDER BY must start with the DISTINCT ON columns. Practice problem 3 uses it.

DISTINCT isn’t free. To find repeats, Postgres sorts or hashes every row. If you reach for DISTINCT because a query gives the same row twice, find out why first. Often a join matched more rows than you meant, and DISTINCT hides it. The lesson on joins shows how that happens.

Climb the ladder

All three sort prices highest first and skip one. What changes is what counts as one price, and what comes back when there’s no second.

Okay

LIMIT 1 OFFSET 1

The idea: Sort the prices highest first, skip one row, keep the next.

It looks right because no two products in the store cost the same at the top.

SELECT price AS second_price
FROM products
ORDER BY price DESC
LIMIT 1 OFFSET 1;

Gets right

Prices that are all different, like the store’s

Gets wrong

Two products at the top price

Why

OFFSET skips one row, and a row isn’t a price: the second 59.00 is still there.

Where it breaks: with the speaker in, the rows go 59.00, 59.00, 45.00, so the second row is 59.00. Okay passes 1 of the 3 checks below.

Better

SELECT DISTINCT price

The idea: Keep each price once, then skip the top one.

The gap in Okay: it counted rows, not prices.

Now the speaker and the headphones are one 59.00, and the second price is 45.00 again.

Highlighted lines are new in this rung.

SELECT DISTINCT price AS second_price
FROM products
ORDER BY price DESC
LIMIT 1 OFFSET 1;

Gets right

Repeated priceswas Prices that are all different, like the store’s

Gets wrong

No second price: no row at all

Why

When every price is the same, DISTINCT leaves one row and OFFSET 1 skips it.

What changed from the previous rung

SELECT price AS second_price
SELECT DISTINCT price AS second_price
FROM products
ORDER BY price DESC
LIMIT 1 OFFSET 1;

Where it breaks: in the sale every product costs 9.99. There’s one price, OFFSET 1 skips it, and the report gets no row instead of one row saying there’s none. Better passes 2 of 3.

Best

A subquery in SELECT

The idea: Put Better inside SELECT ( ... ) AS second_price, so there’s always one row.

The gap in Better: no answer meant no row.

Highlighted lines are new in this rung.

SELECT (
  SELECT DISTINCT price
  FROM products
  ORDER BY price DESC
  LIMIT 1 OFFSET 1
) AS second_price;

Gets right

Every check: one row, NULL when there’s no second pricewas Repeated prices

Gets wrong

Nothing the checks try

Why

A subquery in SELECT that finds no row gives NULL.

What changed from the previous rung

SELECT DISTINCT price AS second_price
FROM products
ORDER BY price DESC
LIMIT 1 OFFSET 1;
SELECT (
  SELECT DISTINCT price
  FROM products
  ORDER BY price DESC
  LIMIT 1 OFFSET 1
) AS second_price;

When Best isn’t best: for the nth-highest price, change OFFSET 1 to OFFSET n - 1. For the second-highest price in each category, you need the answer once per group: DENSE_RANK(), a window function, does that, and the window functions lesson shows how.

Write it yourself

Now write it in the page. Press Run query to see your result and check it. Postgres runs in your browser, so nothing is sent anywhere. Each check changes a few rows first, then they’re put back.

Try Okay from the ladder first, and open a failed check to see what it changed.

The second-highest price

Ctrl + Enter runs

The tables

• products (product_id, name, category, price)

-- The second-highest different price, as second_price. None: NULL. SELECT price FROM products ORDER BY price DESC;

Run queryShow the stepsReset queryLoads about 5 MB the first time. Best on Wi-Fi.

1. ○Not run yet: The store’s data
2. ○Not run yet: A speaker joins electronics at 59.00, the same price as the headphones
   What this check changes

   INSERT INTO products (product_id, name, category, price)
   VALUES (9, 'Speaker', 'electronics', 59.00);

3. ○Not run yet: A sale puts every product at 9.99
   What this check changes

   UPDATE products SET price = 9.99;

Each check runs on its own copy of the data, so your query can’t change the real tables.

Compare

Way	Gets right	Gets wrong	Use it when
Okay: LIMIT 1 OFFSET 1	Prices that are all different	A repeated top price	You want the second row, not the second value
Better: DISTINCT	Repeated prices	No second price gives no row	The caller handles “no row”
Best: SELECT ( ... ) AS second_price	One row every time, NULL when there’s none	Nothing the checks try	A report or app that expects one value

Common mistakes and edge cases

!DISTINCT looks at the whole row

SELECT DISTINCT name, price keeps two products with the same price, because their names differ. To get one row for each price with other columns, pick which row: DISTINCT ON (price) ... ORDER BY price, ..., or group them in the next lessons.

• NULLs count as one. Two visitors with no customer_id become one NULL row. Add WHERE customer_id IS NOT NULL when you don’t want it.
• Spaces and capitals make new values. 'Pune ' isn’t 'Pune'. Clean with trim() and lower() in the query, and better, when the data is saved.
• ORDER BY must use selected columns. With DISTINCT, sorting by a column you didn’t select is an error: “for SELECT DISTINCT, ORDER BY expressions must appear in select list”.
• DISTINCT ON without a full ORDER BY picks any row. ORDER BY department_id alone keeps whichever row of each department the sort puts first, and that can change when any row changes. Add the columns that decide, and a tie-breaker.
• A subquery in SELECT must find at most one row. Leave out LIMIT 1 and Postgres stops: “more than one row returned by a subquery used as an expression”.
• count(DISTINCT customer_id) counts different values, and skips NULL. The lesson on aggregates comes next.

Say it in the interview

Questions to ask first

• The second different price, or the second row? Do two products at the top price count once?
• When there’s no second price, should the query give no row, a NULL, or an error?
• Across all products, or in each category?

A 60-second walkthrough

“I’d SELECT DISTINCT price, ORDER BY price DESC, then LIMIT 1 OFFSET 1. DISTINCT matters: without it, two products at the top price fill the first two rows, and the ‘second’ price is the top price again. Then I’d wrap it as a subquery, SELECT ( … ) AS second_price, so when there’s no second price the query still returns one row, with NULL, instead of no rows. For the nth price I’d use OFFSET n - 1, and for the second price in each category, DENSE_RANK() in a window function.”

Likely follow-ups

• How else can you find it? SELECT max(price) FROM products WHERE price < (SELECT max(price) FROM products). It gives NULL when there’s no second price, too.
• What’s the difference between DISTINCT and GROUP BY? SELECT DISTINCT category and SELECT category FROM products GROUP BY category give the same rows. GROUP BY also lets you count or add up each group.
• Does DISTINCT treat NULLs as equal? Yes: many NULLs become one row. = doesn’t. a IS NOT DISTINCT FROM b compares the way DISTINCT does, so two NULLs match.

Practice

Three problems, from easy to medium. Each has hints, checks you can run here, and its own Okay, Better and Best answers. Try before you open them.

1. The city list (easy)

The sign-up form has a drop-down of the cities customers live in. List each city once, A to Z.

Hint 1 of 3

Each city once: which word goes after SELECT?

Hint 2 of 3

Jon typed the city as Pune with a space at the end. DISTINCT compares text exactly.

Hint 3 of 3

trim(city) removes spaces at both ends. Name it city with AS, and sort by that.

The city list

Ctrl + Enter runs

The tables

• customers (customer_id, name, email, city, signed_up_on)

SELECT city FROM customers;

Run queryShow the stepsReset queryLoads about 5 MB the first time. Best on Wi-Fi.

1. ○Not run yet: The store’s data (row order matters)
2. ○Not run yet: Ivy signs up from Pune, where Asha lives (row order matters)
   What this check changes

   INSERT INTO customers (customer_id, name, email, city, signed_up_on)
   VALUES (9, 'Ivy Nair', 'ivy@example.com', 'Pune', '2026-08-01');

3. ○Not run yet: Jon signs up from Pune, typed with a space at the end (row order matters)
   What this check changes

   INSERT INTO customers (customer_id, name, email, city, signed_up_on)
   VALUES (9, 'Jon Berg', 'jon@example.com', 'Pune ', '2026-08-02');

Each check runs on its own copy of the data, so your query can’t change the real tables.

Show the Okay, Better and Best answers

Okay

Every row’s city

The idea: Sort the customers’ cities A to Z.

Okay passes 1 of 3. When Ivy signs up from Pune, Pune is in the list twice.

SELECT city
FROM customers
ORDER BY city;

Gets right

The store’s data: everyone lives in a different city

Gets wrong

Two customers in one city

Why

One row per customer, so a city with two customers is listed twice.

Better

SELECT DISTINCT city

The idea: Keep each city once.

The gap in Okay: one row per customer, not per city.

Highlighted lines are new in this rung.

SELECT DISTINCT city
FROM customers
ORDER BY city;

Gets right

Two customers in one city

Gets wrong

A city typed with a space at the end

Why

DISTINCT compares text exactly, so 'Pune ' isn’t 'Pune'.

What changed from the previous rung

SELECT city
SELECT DISTINCT city
FROM customers
ORDER BY city;

Where it breaks: Jon types “Pune ” with a space at the end. The list shows Pune twice, and they look the same. Better passes 2 of 3.

Best

SELECT DISTINCT trim(city)

The idea: Remove spaces at both ends first, then keep each city once.

The gap in Better: it compared the city as typed.

Highlighted lines are new in this rung.

SELECT DISTINCT trim(city) AS city
FROM customers
ORDER BY city;

Gets right

Every check

Gets wrong

Nothing the checks try

Why

DISTINCT compares what SELECT gives it, and trim(city) gives 'Pune' for both.

What changed from the previous rung

SELECT DISTINCT city
SELECT DISTINCT trim(city) AS city
FROM customers
ORDER BY city;

When Best isn’t best: “pune” in small letters would still show up apart. Clean the city when it’s saved, and add CHECK (city = trim(city)) so a space can’t get in again. Then plain DISTINCT city is enough.

2. Who put something in their cart (easy)

Marketing wants to email everyone who put something in their cart. From events, list the customer_id of each signed-in customer with an add_to_cart event, once, smallest first. Visitors who aren’t signed in have no customer_id.

Hint 1 of 3

Keep the add_to_cart events, then each customer_id once.

Hint 2 of 3

Two visitors who aren’t signed in both have a NULL customer_id. How many rows does DISTINCT keep for them?

Hint 3 of 3

DISTINCT counts all the NULLs as one value and keeps one NULL row. Add AND customer_id IS NOT NULL.

Who put something in their cart

Ctrl + Enter runs

The tables

• events (event_id, customer_id, event_type, occurred_at)

SELECT customer_id, event_type FROM events;

Run queryShow the stepsReset queryLoads about 5 MB the first time. Best on Wi-Fi.

1. ○Not run yet: The store’s data (row order matters)
2. ○Not run yet: Asha puts a second thing in the cart (row order matters)
   What this check changes

   INSERT INTO events (event_id, customer_id, event_type, occurred_at)
   VALUES (21, 1, 'add_to_cart', '2026-10-21 09:00:00');

3. ○Not run yet: Two visitors add to their carts without signing in (row order matters)
   What this check changes

   INSERT INTO events (event_id, customer_id, event_type, occurred_at)
   VALUES (21, NULL, 'add_to_cart', '2026-10-21 10:00:00'),
          (22, NULL, 'add_to_cart', '2026-10-21 11:00:00');

Each check runs on its own copy of the data, so your query can’t change the real tables.

Show the Okay, Better and Best answers

Okay

Every add_to_cart event

The idea: Keep the add_to_cart events and list their customer_id.

Okay passes 1 of 3. Asha puts a second thing in the cart and gets two emails.

SELECT customer_id
FROM events
WHERE event_type = 'add_to_cart'
ORDER BY customer_id;

Gets right

The store’s data: everyone added to the cart once

Gets wrong

A customer who adds twice

Why

One row per event, so a customer with two events is listed twice.

Better

SELECT DISTINCT customer_id

The idea: Keep each customer once.

The gap in Okay: one row per event, not per customer.

Highlighted lines are new in this rung.

SELECT DISTINCT customer_id
FROM events
WHERE event_type = 'add_to_cart'
ORDER BY customer_id;

Gets right

A customer who adds twice

Gets wrong

Visitors who aren’t signed in

Why

DISTINCT counts NULLs as one value, so the visitors become one NULL row.

What changed from the previous rung

SELECT customer_id
SELECT DISTINCT customer_id
FROM events
WHERE event_type = 'add_to_cart'
ORDER BY customer_id;

Where it breaks: two visitors add to their carts without signing in. The list ends with a NULL, and there’s nobody to email. Better passes 2 of 3.

Best

DISTINCT, without NULL

The idea: Keep only events with a customer, then each customer once.

The gap in Better: DISTINCT kept one NULL.

Highlighted lines are new in this rung.

SELECT DISTINCT customer_id
FROM events
WHERE event_type = 'add_to_cart'
  AND customer_id IS NOT NULL
ORDER BY customer_id;

Gets right

Every check

Gets wrong

Nothing the checks try

Why

WHERE drops the visitors before DISTINCT runs.

What changed from the previous rung

SELECT DISTINCT customer_id
FROM events
WHERE event_type = 'add_to_cart'
  AND customer_id IS NOT NULL
ORDER BY customer_id;

When Best isn’t best: to send the email you need each customer’s address, from customers. That’s a join, in the joins lesson. A join can repeat rows too, so check why before you add DISTINCT.

3. The best-paid person in each department (medium)

HR wants the best-paid person in each department: department_id, name and salary, by department_id. If two people in a department are paid the same, show the one hired first.

Hint 1 of 3

DISTINCT ON (department_id) keeps one row for each department: the first one in ORDER BY order.

Hint 2 of 3

ORDER BY department_id alone says nothing about which person in a department comes first. Which column should?

Hint 3 of 3

ORDER BY department_id, salary DESC, then hired_on for two people paid the same.

The best-paid person in each department

Ctrl + Enter runs

The tables

• employees (employee_id, name, department_id, manager_id, salary, hired_on)

SELECT department_id, name, salary, hired_on FROM employees ORDER BY department_id;

Run queryShow the stepsReset queryLoads about 5 MB the first time. Best on Wi-Fi.

1. ○Not run yet: The store’s data (row order matters)
2. ○Not run yet: Sam gets a raise to 9,800, more than Meera (row order matters)
   What this check changes

   UPDATE employees SET salary = 9800 WHERE employee_id = 4;

3. ○Not run yet: Omar gets a raise to 6,100, the same as Lena, who was hired first (row order matters)
   What this check changes

   UPDATE employees SET salary = 6100 WHERE employee_id = 6;

Each check runs on its own copy of the data, so your query can’t change the real tables.

Show the Okay, Better and Best answers

Okay

DISTINCT ON, sorted by department

The idea: Keep the first row of each department.

Okay passes 1 of 3. After Sam’s raise it still shows Meera for department 1, and now Omar, paid 4,300, for department 2. One changed row moved the rows around, and DISTINCT ON kept whichever came first.

SELECT DISTINCT ON (department_id) department_id, name, salary
FROM employees
ORDER BY department_id;

Gets right

The store’s data, by luck

Gets wrong

Anyone whose pay changes

Why

Nothing says which row of a department comes first, so DISTINCT ON keeps whichever the sort happens to put first.

Better

Then salary DESC

The idea: Sort each department’s people by pay, highest first.

The gap in Okay: the first row was whoever was stored first.

Highlighted lines are new in this rung.

SELECT DISTINCT ON (department_id) department_id, name, salary
FROM employees
ORDER BY department_id, salary DESC;

Gets right

Who’s paid most

Gets wrong

Two people paid the same

Why

A tie on salary leaves either person first.

What changed from the previous rung

SELECT DISTINCT ON (department_id) department_id, name, salary
FROM employees
ORDER BY department_id;
ORDER BY department_id, salary DESC;

Where it breaks: Omar’s raise makes Omar paid the same as Lena, and Postgres shows Omar, though Lena was hired first. Better passes 2 of 3.

Best

Then hired_on

The idea: Break a tie on pay with the hiring date.

The gap in Better: ties on pay.

Highlighted lines are new in this rung.

SELECT DISTINCT ON (department_id) department_id, name, salary
FROM employees
ORDER BY department_id, salary DESC, hired_on;

Gets right

Every check

Gets wrong

Nothing the checks try

Why

ORDER BY now says which row of each department comes first.

What changed from the previous rung

SELECT DISTINCT ON (department_id) department_id, name, salary
FROM employees
ORDER BY department_id, salary DESC;
ORDER BY department_id, salary DESC, hired_on;

When Best isn’t best: DISTINCT ON is Postgres only. Elsewhere, number the rows with ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC, hired_on) and keep number 1, as in each customer’s latest order. If two people could be hired on the same day too, add employee_id last.

Check yourself

A few quick questions. Ones you miss are remembered, so you can find your weak spots later.

1. Predict the outputproducts holds four prices. What does this query return?

   -- prices: 59.00, 45.00, 59.00, 24.99
   SELECT price
   FROM products
   ORDER BY price DESC
   LIMIT 1 OFFSET 1;

   45.0024.9959.00No row

   Check answer
2. Multiple choiceA table has five people. Three have city NULL and two have city 'Pune'. How many rows does SELECT DISTINCT city give?

   2345

   Check answer
3. Spot the bugThis should list each customer once, most recent event first. What happens?

   SELECT DISTINCT customer_id
   FROM events
   ORDER BY occurred_at DESC;

   Each customer once, newest firstEach customer once, in any orderEvery event, newest firstPostgres stops with an error

   Check answer
4. Multiple choiceStationery has three products, at 4.50, 3.20 and 4.50. How many stationery rows does SELECT DISTINCT category, price FROM products give?

   123

   Check answer
5. Multiple choiceNo product costs over 1000. What does SELECT (SELECT price FROM products WHERE price > 1000) AS big; give?

   No rowsAn errorOne row: 0One row: NULL

   Check answer

Flashcards

Add these to your review deck. Each card comes back a little later every time you get it right.

• Card 1Where does DISTINCT run among a query’s clauses?Show answer
• Card 2Does DISTINCT compare one column or the whole row?Show answer
• Card 3How does DISTINCT treat NULLs?Show answer
• Card 4How do you get the second-highest different price, with NULL when there’s none?Show answer
• Card 5What does DISTINCT ON (a) keep?Show answer
• Card 6With DISTINCT, why can’t ORDER BY use a column you didn’t select?Show answer

Add 6 cards to my review deck

Cheat sheet

• SELECT DISTINCT a, b keeps one row for each different (a, b). It runs after SELECT, before ORDER BY.
• Same means exactly the same: spaces and capitals count, 59 is 59.00, and NULLs count as one.
• With DISTINCT, ORDER BY can only use selected columns.
• Second-highest: SELECT DISTINCT ... ORDER BY ... DESC LIMIT 1 OFFSET 1, inside SELECT ( ... ) for NULL when there’s none.
• DISTINCT ON (a) ... ORDER BY a, b DESC, tie-breaker keeps the first row of each a. Postgres only.
• Duplicates you didn’t expect? Find out why before you add DISTINCT.

Go deeper

• In the PostgreSQL docs: DISTINCT, DISTINCT ON in SELECT and scalar subqueries.
• Try your own prices: the second-highest price on the SQL visualizer.
• Next on the SQL roadmap: the whole map, from your first SELECT to window functions.

Can you do these now?

If you’re not sure of any of these, scroll back to that part before moving on.

• Use DISTINCT to keep one row for each different row, and say where it runs among the clauses
• Say what DISTINCT counts as the same, including NULLs and text with spaces
• Return one row with NULL when a query finds nothing, using a subquery in SELECT
• Keep the first row of each group with DISTINCT ON and a tie-breaker

✓Mark as completeI already know this

Status: Not started