ORDER BY & LIMIT: Top Charts

Sorting with ORDER BY

Without ORDER BY, the database returns rows in whatever order is convenient for it. Never rely on that! ORDER BY column sorts ascending (A→Z, small→big). Add DESC for descending.

Example: Priciest first

SELECT name, price FROM pizzas ORDER BY price DESC;

Remove DESC (or write ASC) to flip it.

Sort by several columns

List multiple columns and SQL uses the second as a tie-breaker for the first, and so on. Each one can have its own direction.

Example: By category, then cheapest inside each category

SELECT category, name, price FROM pizzas ORDER BY category, price;

All the Classic pizzas group together, sorted by price within the group.

LIMIT: just the top N

LIMIT n returns only the first n rows. Combined with ORDER BY this gives you "top 3", "cheapest 5", and so on. OFFSET m skips m rows first. That is how pagination works.

Example: The 3 most loyal customers

SELECT name, loyalty_points FROM customers ORDER BY loyalty_points DESC LIMIT 3;

Add OFFSET 3 at the end to see customers ranked 4 to 6.

The order of clauses matters

SQL is picky about clause order: SELECT → FROM → WHERE → ORDER BY → LIMIT. Put them out of order and the database will complain.

Example: All four together

SELECT name, calories FROM pizzas WHERE is_vegetarian = 0 ORDER BY calories DESC LIMIT 2;

The two most calorific non-vegetarian pizzas. Filter first, then sort, then cut.

Practice exercises

1. Alphabetical menu

   Show every pizza name, sorted alphabetically (A to Z).

2. Veteran chefs

   Show chef name and years_experience, most experienced first.

3. Lightest three

   Show the name and calories of the 3 lowest-calorie pizzas.

4. Newest customers, page two

   Sort customers by joined_on, newest first, then skip the first 2 and show the next 3. Return name and joined_on.

5. Priciest Wild pizza

   Show the name and price of the single most expensive pizza in the 'Wild' category.

Open this page in a browser to run your SQL and get instant, auto-graded feedback.

Boss battle

Take the ORDER BY & LIMIT quiz: 5 timed questions.