ORDER BY decides the order of the rows a query returns, and LIMIT decides how many of them you get. Used together they answer "top N" questions: the three highest scorers, the latest ten orders, page 4 of a product list. Without ORDER BY, MySQL gives no promise about row order at all, so a LIMIT on its own returns "some" rows, not the "first" ones.
Sample data: internal test results
CREATE TABLE results (
roll_no INT PRIMARY KEY,
name VARCHAR(20),
branch VARCHAR(5),
marks INT,
submitted_at TIME
);
INSERT INTO results VALUES
(101,'Ishaan','CSE',88,'10:42:00'), (102,'Pooja','ECE',92,'10:15:00'),
(103,'Farhan','CSE',92,'10:31:00'), (104,'Lavanya','ME',75,'10:05:00'),
(105,'Gurpreet','ECE',NULL,NULL), (106,'Ananya','CSE',81,'10:50:00'),
(107,'Tenzin','ME',88,'10:22:00');
Gurpreet was absent, so his marks are NULL. Two pairs of students are tied. Both details matter below.
Sorting by one column, then by a tie-breaker
SELECT name, marks, submitted_at
FROM results
ORDER BY marks DESC, submitted_at ASC;
| name | marks | submitted_at |
|---|---|---|
| Pooja | 92 | 10:15:00 |
| Farhan | 92 | 10:31:00 |
| Tenzin | 88 | 10:22:00 |
| Ishaan | 88 | 10:42:00 |
| Ananya | 81 | 10:50:00 |
| Lavanya | 75 | 10:05:00 |
| Gurpreet | NULL | NULL |
Rows are sorted by marks first. Only where marks are equal does submitted_at decide, so whoever submitted first ranks higher. ASC is the default and can be left out. Each column takes its own direction; DESC applies only to the column right before it.
Where do NULLs go?
MySQL treats NULL as smaller than any value. It comes first in ascending order and last in descending order:
SELECT name, marks FROM results ORDER BY marks LIMIT 3;
| name | marks |
|---|---|
| Gurpreet | NULL |
| Lavanya | 75 |
| Ananya | 81 |
MySQL has no NULLS LAST keyword. Sort on the expression marks IS NULL first. It is 0 for real values and 1 for NULL, so NULLs drop to the bottom:
SELECT name, marks FROM results ORDER BY marks IS NULL, marks LIMIT 3;
| name | marks |
|---|---|
| Lavanya | 75 |
| Ananya | 81 |
| Ishaan | 88 |
For filling or filtering NULLs, see handling NULL values in MySQL.
LIMIT for top-N, and the tie problem
SELECT name, marks FROM results ORDER BY marks DESC LIMIT 3;
| name | marks |
|---|---|
| Pooja | 92 |
| Farhan | 92 |
| Ishaan | 88 |
Tenzin also scored 88 but is cut off. Which of the two 88s appears is left to chance, because nothing in the ORDER BY separates them. A different MySQL version or plan could return Tenzin instead. Two honest fixes:
- Add a unique tie-breaker (
ORDER BY marks DESC, roll_no) so the result is always the same. - If ties should all be included, ask for "everyone scoring at least the 3rd-highest mark":
SELECT name, marks FROM results
WHERE marks >= (SELECT marks FROM results ORDER BY marks DESC LIMIT 1 OFFSET 2)
ORDER BY marks DESC;
| name | marks |
|---|---|
| Pooja | 92 |
| Farhan | 92 |
| Ishaan | 88 |
| Tenzin | 88 |
RANK() and DENSE_RANK() handle ties more flexibly. The differences are shown in RANK vs DENSE_RANK vs ROW_NUMBER.
OFFSET for pages
SELECT name, marks FROM results ORDER BY marks DESC, roll_no LIMIT 3 OFFSET 3;
-- same thing, older syntax: LIMIT offset, count
SELECT name, marks FROM results ORDER BY marks DESC, roll_no LIMIT 3, 3;
| name | marks |
|---|---|
| Tenzin | 88 |
| Ananya | 81 |
| Lavanya | 75 |
Both return rows 4 to 6. In LIMIT 3, 3 the first number is the offset, and swapping the two numbers by mistake is very common. For page p with n rows per page, the offset is (p - 1) * n.
Large offsets get slow, because MySQL still reads and throws away every skipped row. On big tables, remember the last key you showed and continue from it ("keyset" pagination). With an index on the sort column, this stays fast however deep you go:
SELECT roll_no, name FROM results
WHERE roll_no > 103 -- last roll_no on the previous page
ORDER BY roll_no
LIMIT 2;
| roll_no | name |
|---|---|
| 104 | Lavanya |
| 105 | Gurpreet |
Top N per group
LIMIT applies to the whole result, so it cannot give "the topper of each branch". Number the rows within each branch with a window function, then keep row 1:
SELECT branch, name, marks
FROM (
SELECT branch, name, marks,
ROW_NUMBER() OVER (PARTITION BY branch ORDER BY marks DESC, roll_no) AS rn
FROM results
WHERE marks IS NOT NULL
) t
WHERE rn = 1
ORDER BY branch;
| branch | name | marks |
|---|---|---|
| CSE | Farhan | 92 |
| ECE | Pooja | 92 |
| ME | Tenzin | 88 |
Change rn = 1 to rn <= 2 for the top two per branch. This needs MySQL 8.0. The window functions tutorial covers PARTITION BY in depth.
Custom order
SELECT name, branch FROM results ORDER BY FIELD(branch, 'ME', 'ECE', 'CSE'), name;
| name | branch |
|---|---|
| Lavanya | ME |
| Tenzin | ME |
| Gurpreet | ECE |
| Pooja | ECE |
| Ananya | CSE |
| Farhan | CSE |
| Ishaan | CSE |
FIELD() returns the position of the value in the list. Use it for business orders such as 'High', 'Medium', 'Low' that are not alphabetical.
Quick rules
- Clause order is
SELECT … FROM … WHERE … GROUP BY … HAVING … ORDER BY … LIMIT. - You can sort by a column alias or by an expression.
ORDER BY 2(column position) also works, but it breaks silently when someone edits the select list. - An index on the
ORDER BYcolumn lets MySQL read rows already in order and stop afterLIMITrows, instead of sorting everything. MySQL indexes explained shows how.