Ravindra BagaleCourses & study guides Track your progress

5. ORDER BY, DISTINCT and Top-N Results

5.4 How to Get Top N Rows with ORDER BY and LIMIT

By Last updated:

Why. Top-N is a classic interview ask.

What. ORDER BY … DESC LIMIT n gives the top n rows by that sort.

How. Always sort first, then LIMIT.

Steps

  1. Top 3 amounts.
  2. Top 5 latest dates (ORDER BY date DESC).
  3. Say the business meaning in one line.

Examples

Example 1 · dataset: raja_coffee

SELECT order_id, city, amount_inr
FROM orders
ORDER BY amount_inr DESC
LIMIT 3;

Output

order_idcityamount_inr
1016Pune1780.0
1013Pune1680.0
1007Nashik1080.0

Meaning. Top 3 coffee orders by INR amount.

Ravindra Bagale's Tip

Ties: LIMIT 3 may hide a 4th row with the same amount — know that for interviews.