5. Sorting and limiting results

Results come back in no guaranteed order unless you ask for one. ORDER BY sorts; LIMIT trims.

ORDER BY

Sort by one or more columns. ASC (ascending, the default) goes small to large or A→Z; DESC reverses it:

SELECT title, rental_rate
FROM film
ORDER BY rental_rate DESC;

Sort by several columns to break ties. Here films are grouped by rating, and within each rating sorted by length:

SELECT title, rating, length
FROM film
ORDER BY rating, length DESC;

LIMIT

LIMIT keeps just the first N rows. Combined with ORDER BY, it answers "top N" questions — the 10 longest films:

SELECT title, length
FROM film
ORDER BY length DESC, title
LIMIT 10;

Notice the extra title in the sort. Lots of films share the same length, so ORDER BY length DESC alone leaves ties in an unpredictable order — and which 10 you get could change run to run. Adding a tie-breaker that's unique (like title) makes the result stable. This matters any time you use LIMIT.

OFFSET for paging

OFFSET skips rows before LIMIT takes them — the basis of page 2, page 3, and so on:

SELECT title
FROM film
ORDER BY title
LIMIT 5 OFFSET 5;   -- rows 6–10

Try it

Show the title and rental_rate of the 5 cheapest films. Sort by rental_rate ascending, then by title to break ties.

SELECT title, rental_rate
FROM film
-- sort and limit

SELECT title, rental_rate
FROM film
ORDER BY rental_rate, title
LIMIT 5;