SQL ORDER BY and LIMIT

Rows in a table have no promised order. If you run the same query twice, you might get the rows in a different sequence. When order matters, such as a leaderboard or a “top 10” list, you must ask for it with ORDER BY.

LIMIT then trims the result to the first few rows. Used together, they answer questions like “who are the top three scorers?”

Sorting and Taking the Top Rows

Take a tournament_runs table with one row per player. Nikhil and Siddharth have both scored 612 runs, so we will need a tie-breaker.

SELECT player, team, runs
FROM tournament_runs
ORDER BY runs DESC, player ASC
LIMIT 3;

Output:

Code Explanation:

• ORDER BY runs DESC puts the highest scores first. Without DESC, the sort runs from low to high, because ASC is the default.
• The two players on 612 are tied, so the second sort key, player ASC, puts Nikhil before Siddharth alphabetically.
• LIMIT 3 keeps only the first three rows of the sorted result.

The order of the clauses matters. ORDER BY comes before LIMIT, because the database must sort first and cut afterwards.

Moving Through the Results

To see the next group of rows, add OFFSET. It skips a number of rows before LIMIT starts counting. Changing the query to LIMIT 3 OFFSET 3 skips the top three and returns the next three: Zaid (530), Imran (498), and Tarun (455).

This is how websites show results page by page, and it is also a good habit when you only want a quick peek at a huge table.

Note: PostgreSQL and MySQL both use LIMIT. SQL Server uses SELECT TOP 3 instead, and Oracle uses FETCH FIRST 3 ROWS ONLY.