ORDER BY and LIMIT: the bestseller list

End of the evening. The boss leans over the counter and asks: what were our three bestsellers tonight? That question has two halves. First, rank everything. Then, stop after three. SQL gives each half its own word: ORDER BY ranks, LIMIT cuts. Together they build every top-ten list, every leaderboard, every “most popular” shelf you have ever seen.

The database picks the order, unless you do

Here is a small surprise: a plain SELECT promises you the right rows, but it promises nothing about their order. Rows come back however the kitchen happens to hand them over. To choose the order, say so:

SELECT item, qty FROM orders ORDER BY qty;

ORDER BY qty sorts the rows by that column, smallest first. Smallest first is the default, called ASC (ascending). For a bestseller list you want the big numbers on top, so you flip it with DESC (descending):

SELECT item, qty FROM orders ORDER BY qty DESC;

LIMIT cuts the list

Ranking six rows is nice. Ranking six million is noise, if all you wanted was the podium. LIMIT n keeps the first n rows of the result and drops the rest. Put the two together and you get the pattern:

SELECT item, qty FROM orders ORDER BY qty DESC LIMIT 3;

fig 1 · ORDER BY ranks the plates, then LIMIT 3 cuts the list

Watch the order of operations in the figure. The plates from lesson 02 arrive in kitchen order, get ranked by qty DESC, and only then does the cut line fall. The rows below it are not deleted, they just never make it onto the result. Sort first, cut second: that is ORDER BY x DESC LIMIT n, the top-N pattern.

One warning, and it matters: LIMIT on its own still cuts, but without ORDER BY there is no ranking first. You get some n rows, chosen by nobody in particular.

Sorting by more than one column

Look at the figure again: maki · 2 and nigiri · 2 are tied. Who gets second place? Without further instructions, the database shrugs. You can break ties by listing a second column:

SELECT item, qty FROM orders ORDER BY qty DESC, item;

Read it left to right: sort by qty descending, and whenever two rows tie on qty, sort those by item alphabetically. Each column can carry its own direction, so ORDER BY item, qty DESC is fine too: group the menu alphabetically, biggest order first inside each name.

Ranking your stacks

Here is where it gets good. ORDER BY does not care where the rows came from, so it happily ranks the summary rows you built with GROUP BY in lesson 03:

SELECT item, SUM(qty) FROM orders GROUP BY item ORDER BY SUM(qty) DESC LIMIT 1;

In plain words: stack the orders by item, total each stack, rank the stacks by their total, keep the winner. That single line is “what was our bestseller tonight?”, answered for real. Group, aggregate, rank, cut: four small words, one very employable sentence.

Try this yourself

Rebuild the table from lesson 02 in your playground, then run the bestseller list:

CREATE TABLE orders (item TEXT, qty INTEGER, tbl INTEGER);
INSERT INTO orders VALUES
  ('maki', 2, 5), ('nigiri', 1, 3), ('temaki', 1, 5),
  ('maki', 1, 2), ('gunkan', 3, 5), ('nigiri', 2, 1);

SELECT item, qty FROM orders ORDER BY qty DESC LIMIT 3;

You should see gunkan on top with 3, exactly like the figure. Then answer these with queries: list the menu alphabetically (ORDER BY item). Find the single biggest order with LIMIT 1. Break the tie at qty 2 so maki always beats nigiri. And the boss question: which table ordered the most pieces tonight? You will need GROUP BY tbl, a SUM, and everything from this lesson.