Sorting & Limiting Results

MySQL doesn't promise any particular row order unless you ask for one, and it will happily hand back every matching row unless you tell it when to stop. ORDER BY controls the first, and LIMIT controls the second - together they turn a raw result set into exactly the slice of data a StreamHub page actually wants to display.

ORDER BY - Sorting Results

By default, ORDER BY sorts ascending (smallest or earliest first). Add DESC to flip that around. Here's StreamHub's catalog sorted newest-first:

SELECT title, release_year
FROM movies
ORDER BY release_year DESC;

Sorting by Multiple Columns

List several columns, in priority order, separated by commas. Ties in the first column are broken by the second. Here are StreamHub's reviews grouped by movie, then ranked best-to-worst within each movie:

SELECT movie_id, rating, review_date
FROM reviews
ORDER BY movie_id, rating DESC;

Notice each movie_id group stays together, and within every group the highest rating comes first - that's DESC applying only to the second sort key, exactly as written.

LIMIT - Capping the Number of Rows

Add LIMIT to cut a result set down to a fixed number of rows. StreamHub's 10 most recent reviews, site-wide:

SELECT movie_id, rating, review_date
FROM reviews
ORDER BY review_date DESC
LIMIT 10;

No matter how many thousands of reviews StreamHub has collected, this always returns exactly 10 rows - the most recent ones.

LIMIT with OFFSET - Pagination

Add OFFSET to skip a number of rows before starting to return results - the standard way to fetch a specific "page" of data. Here's page 2 of StreamHub's movie list, 20 movies per page (so page 2 skips the first 20 and returns the next 20):

SELECT title, release_year
FROM movies
ORDER BY title
LIMIT 20 OFFSET 20;

MySQL also accepts a shorthand with no OFFSET keyword - the same query written that way:

SELECT title, release_year
FROM movies
ORDER BY title
LIMIT 20, 20;

This is conceptually the exact same problem solved at the application layer in flask-querying-filtering-pagination.php and django-pagination.php elsewhere on this site - there, a framework builds this same LIMIT/OFFSET query for you behind a page number. Here, it's happening directly in the database, which is exactly what those frameworks are doing under the hood.

Combining ORDER BY and LIMIT - "Top N" Queries

Sorting and limiting together produce one of the most common query patterns in real applications: the "top N" query. StreamHub's 5 highest-rated movies, best first:

SELECT m.title, AVG(r.rating) AS avg_rating
FROM movies m
JOIN reviews r ON r.movie_id = m.id
GROUP BY m.id
ORDER BY avg_rating DESC
LIMIT 5;

Notice this query needed AVG() and GROUP BY just to compute "average rating per movie" before ORDER BY and LIMIT could even act on it - we cover both properly in Aggregate Functions and GROUP BY & HAVING, coming up soon in this track.