BBampaa

Lessons › SQL › Go further in SQL: Business questions

SQL lesson

What's the second-highest order?

What's the second-highest order? First give every order a rank, highest first, then keep rank 2. Use DENSE_RANK: tied orders share a rank, and the next rank follows straight on, so "second" always means the second-highest value.

Try this lesson free →
About 5–10 minutes · no sign-up needed

Example

Rank first, then pick the rank

WITH ranked AS (
  SELECT order_id,
         revenue,
         DENSE_RANK() OVER (ORDER BY revenue DESC) AS rnk
  FROM orders
)
SELECT order_id, revenue
FROM ranked
WHERE rnk = 2

Result:

order_idrevenue
5120

Step by step

  1. More rows if orders tie.
    WITH ranked AS (SELECT order_id, revenue, DENSE_RANK() OVER (ORDER BY revenue DESC) AS rnk FROM orders) SELECT order_id, revenue FROM ranked WHERE rnk = 2
  2. No join.
    WITH ranked AS (SELECT order_id, revenue, DENSE_RANK() OVER (ORDER BY revenue DESC) AS rnk FROM orders) SELECT order_id, revenue FROM ranked WHERE rnk = 2
  3. WHERE rnk = 2, after ranking.
    WITH ranked AS (SELECT order_id, revenue, DENSE_RANK() OVER (ORDER BY revenue DESC) AS rnk FROM orders) SELECT order_id, revenue FROM ranked WHERE rnk = 2
  4. DENSE_RANK() OVER (ORDER BY revenue DESC) in a CTE.
    WITH ranked AS (SELECT order_id, revenue, DENSE_RANK() OVER (ORDER BY revenue DESC) AS rnk FROM orders) SELECT order_id, revenue FROM ranked WHERE rnk = 2

Practice

Try each one in your head first, then open the answer.

1. What's the second-highest order? Show order_id and revenue.
WITH ranked AS (SELECT order_id, revenue, DENSE_RANK() OVER (ORDER BY revenue DESC) AS rnk FROM orders) SELECT order_id, revenue FROM ranked WHERE rnk = 2
2. Which orders have the fifth-highest revenue? Show order_id and revenue.
WITH ranked AS (SELECT order_id, revenue, DENSE_RANK() OVER (ORDER BY revenue DESC) AS rnk FROM orders) SELECT order_id, revenue FROM ranked WHERE rnk = 5
3. What's the second-highest revenue? Show just the number, as revenue.
WITH ranked AS (SELECT order_id, revenue, DENSE_RANK() OVER (ORDER BY revenue DESC) AS rnk FROM orders) SELECT DISTINCT revenue FROM ranked WHERE rnk = 2
4. Which store has the second-highest total revenue? Show store and revenue.
WITH totals AS (SELECT store, SUM(revenue) AS revenue FROM orders GROUP BY store), ranked AS (SELECT store, revenue, DENSE_RANK() OVER (ORDER BY revenue DESC) AS rnk FROM totals) SELECT store, revenue FROM ranked WHERE rnk = 2
5. Which customer has the second-highest total spend? Show customer_id and spend.
WITH totals AS (SELECT customer_id, SUM(revenue) AS spend FROM orders GROUP BY customer_id), ranked AS (SELECT customer_id, spend, DENSE_RANK() OVER (ORDER BY spend DESC) AS rnk FROM totals) SELECT customer_id, spend FROM ranked WHERE rnk = 2

Learn it by doing. This lesson is a short hands-on level on Bampaa: see it work, then build and write it yourself, with instant feedback.

Try this lesson free →
About 5–10 minutes · no sign-up needed