Lessons · SQL · the top one, by order
The top of a sorted list
ORDER BY sorts the result; LIMIT keeps the first N rows of that order. Together they answer 'the biggest', 'the latest', 'the top five'.
Hone is a place to practise programming. This is one of its lessons, written out in full and free to read without an account.
What it is for
The most recent order, the ten best-rated films, the oldest open ticket: every dashboard has a top-N, and it is always sort then cut.
How to think about it
Decide the order first (DESC for biggest or latest first), then the count. Without ORDER BY, LIMIT keeps an arbitrary N rows, which looks fine and is wrong.
Worked example
SELECT title, year FROM filmsWhat to show.
ORDER BY year DESCNewest first.
LIMIT 3;Keep the top three of that order.
Your turn
The single most expensive product.
SELECT name FROM products ORDER BY price LIMIT 1;
Run a query against real tables
The trap
LIMIT without ORDER BY is 'any N rows'. It happens to look sorted on small tables and stops looking sorted in production.
Practise the top one, by order on HoneA question on it now, a coding challenge where there is one, and it is remembered for review. Free, no email needed.