Second highest invoice amount

Question

A billing dashboard shows the runner-up invoice as a sanity check against the largest one.

From `invoices`, return a single column `second_highest_amount` containing the **second highest distinct** `amount`. If fewer than two distinct amounts exist the query should return `NULL` rather than no rows

Accepted Answer

Step 1: Deduplicate the amounts first — two accounts share the top amount of 126500, and “second highest distinct” must skip that tie. Step 2: Sort the distinct amounts descending, then skip one row with `OFFSET 1` and take one row with `LIMIT 1`. Step 3: Wrap that query as a **scalar subquery** in the `SELECT` list. A scalar subquery that finds nothing yields `NULL`, which satisfies the “return NULL, not zero rows” requirement. Step 4: Alias the result as `second_highest_amount`.

Second highest invoice amount

More Subqueries questions

Asked at PayPal