ORDER BY and LIMIT
40 min · Beginner
Need help? Ask a tutor on WhatsApp (opens in a new tab)A table has no fixed order of rows. ORDER BY puts the result in the order you ask for, and LIMIT cuts it down to the first few rows or to one page of results.
Sorting ascending and descending
ORDER BY sorts the result rows by one or more columns. ASC, the default, goes small to large, A to Z, old to new; DESC goes the other way. Without ORDER BY, the order of rows is not promised.
A list on screen needs a useful order: cheapest first, newest first. The example keeps the books of the store and sorts them by price, first ascending, then descending.
Example 1 · Cheapest and most expensive first
SELECT name, priceFROM productsWHERE category_id = 5ORDER BY price;
SELECT name, priceFROM productsWHERE category_id = 5ORDER BY price DESC;+-----------------+--------+ | name | price | +-----------------+--------+ | Atomic Habits | 399.00 | | Clean Code | 499.00 | | Hands-On Python | 650.00 | +-----------------+--------+ +-----------------+--------+ | name | price | +-----------------+--------+ | Hands-On Python | 650.00 | | Clean Code | 499.00 | | Atomic Habits | 399.00 | +-----------------+--------+
What this code does
- Both queries keep only the books (category 5), then sort them. Ascending is the default, so the first query lists the cheapest book first.
DESCreverses the order.WHEREalways comes beforeORDER BY: filter first, then sort.
Without ORDER BY, the rows often look like id order, but that order is never promised.
ORDER BY price: cheapest first (same asORDER BY price ASC).ORDER BY price DESC: most expensive first.ORDER BY order_date DESC: latest orders first.- Text sorts in dictionary order and, with the default collation, ignores case.
Sorting by several columns
A tie-breaker is a second sort column that orders the rows which are equal in the first one, as in ORDER BY points DESC, nrr DESC. Without one, tied rows may come back in any order.
Ties are common: two IPL teams on 16 points, two employees on the same salary. A second sort column settles them, and each column gets its own ASC or DESC.
Example 2 · IPL points table: points, then net run rate
DROP TABLE IF EXISTS ipl_points;CREATE TABLE ipl_points ( team VARCHAR(30) PRIMARY KEY, played INT NOT NULL, won INT NOT NULL, points INT NOT NULL, nrr DECIMAL(5,3) NOT NULL);INSERT INTO ipl_points VALUES ('Chennai Super Kings', 14, 8, 16, 0.528), ('Mumbai Indians', 14, 7, 14, -0.212), ('Gujarat Titans', 14, 9, 18, 0.809), ('Rajasthan Royals', 14, 8, 16, 0.148), ('Kolkata Knight Riders',14, 7, 14, 0.390), ('Punjab Kings', 14, 5, 10, -0.304);
SELECT team, points, nrrFROM ipl_pointsORDER BY points DESC, nrr DESC;+-----------------------+--------+--------+ | team | points | nrr | +-----------------------+--------+--------+ | Gujarat Titans | 18 | 0.809 | | Chennai Super Kings | 16 | 0.528 | | Rajasthan Royals | 16 | 0.148 | | Kolkata Knight Riders | 14 | 0.390 | | Mumbai Indians | 14 | -0.212 | | Punjab Kings | 10 | -0.304 | +-----------------------+--------+--------+
What this code does
- Teams are sorted by points first. Chennai and Rajasthan both have 16, so the second column, net run rate (
nrr), breaks the tie: Chennai's 0.528 beats 0.148. - Kolkata and Mumbai tie on 14 the same way.
DESCis written twice because each column has its own direction.
Example 3 · Sort by department, salary and an alias
SELECT dept_id, name, salaryFROM employeesWHERE dept_id IN (1, 2)ORDER BY dept_id, salary DESC, name;
SELECT name, price * stock AS stock_valueFROM productsWHERE category_id = 4ORDER BY stock_value DESC;+---------+-------------+-----------+ | dept_id | name | salary | +---------+-------------+-----------+ | 1 | Vikram Rao | 250000.00 | | 1 | Anita Desai | 180000.00 | | 1 | Karthik Raj | 120000.00 | | 1 | Pooja Gupta | 120000.00 | | 1 | Suresh Babu | 95000.00 | | 2 | Neha Joshi | 150000.00 | | 2 | Amit Shah | 85000.00 | | 2 | Farah Ali | 85000.00 | +---------+-------------+-----------+ +------------------------+-------------+ | name | stock_value | +------------------------+-------------+ | boAt Airdopes 141 | 155880.00 | | Mi Power Bank 20000mAh | 131940.00 | | Logitech M331 Mouse | 0.00 | +------------------------+-------------+
What this code does
- Inside each department, the highest salary comes first. Karthik Raj and Pooja Gupta both earn 120000, so
nameis the final tie-breaker; the same happens for Amit and Farah in Sales. ORDER BY stock_valueuses the alias from the SELECT list. That is allowed here, because sorting happens after the SELECT list is built.
ORDER BY may also use a SELECT alias, such as ORDER BY stock_value DESC. This works, unlike in WHERE, because MySQL sorts after it has built the SELECT list.
DESC applies only to the column just before it. ORDER BY points, nrr DESC sorts points ascending and only nrr descending.Where NULLs go when sorting
In MySQL, NULL counts as smaller than every value. So NULLs come first in an ascending sort and last in a descending sort.
Tanvi Kulkarni has no department, so a list sorted by dept_id shows her at the very top. The example sorts five employees, then moves the NULL row to the end.
Example 4 · NULLs first, and how to move them last
SELECT name, dept_idFROM employeesWHERE emp_id IN (1, 6, 9, 11, 13)ORDER BY dept_id;
SELECT name, dept_idFROM employeesWHERE emp_id IN (1, 6, 9, 11, 13)ORDER BY dept_id IS NULL, dept_id;+----------------+---------+ | name | dept_id | +----------------+---------+ | Tanvi Kulkarni | NULL | | Vikram Rao | 1 | | Neha Joshi | 2 | | Lakshmi Menon | 3 | | Gita Reddy | 4 | +----------------+---------+ +----------------+---------+ | name | dept_id | +----------------+---------+ | Vikram Rao | 1 | | Neha Joshi | 2 | | Lakshmi Menon | 3 | | Gita Reddy | 4 | | Tanvi Kulkarni | NULL | +----------------+---------+
What this code does
- In an ascending sort, MySQL puts NULL before every value, so Tanvi Kulkarni, with no department, comes first.
dept_id IS NULLis 0 for the others and 1 for Tanvi. Sorting by it first moves her to the end, anddept_idthen orders the rest.
To push NULLs to the end of an ascending sort, sort first by the test dept_id IS NULL, which is 0 for rows with a value and 1 for NULL rows, and then by the column itself.
ORDER BY dept_id DESC: ...3, 2, 1, then NULL last.ORDER BY dept_id IS NULL, dept_id: 1, 2, 3..., then NULL last.
LIMIT and OFFSET for top N and paging
LIMIT n returns at most n rows of the result, and OFFSET k skips the first k rows before that. Together they give top-N lists and pages. LIMIT always comes last in a query.
A shop page shows the 3 dearest products, or 4 customers per page. Sort first, then cut: LIMIT keeps the first rows and OFFSET skips the rows of the earlier pages.
Example 5 · Top 3 and page by page
-- Top 3 most expensive productsSELECT name, priceFROM productsORDER BY price DESCLIMIT 3;
-- Customers, 4 per page: page 1 and page 2SELECT customer_id, nameFROM customersORDER BY customer_idLIMIT 4 OFFSET 0;
SELECT customer_id, nameFROM customersORDER BY customer_idLIMIT 4 OFFSET 4;+-----------------------+----------+ | name | price | +-----------------------+----------+ | iPhone 15 | 69900.00 | | HP Victus Gaming | 64990.00 | | Lenovo IdeaPad Slim 3 | 42990.00 | +-----------------------+----------+ +-------------+-------------+ | customer_id | name | +-------------+-------------+ | 1 | Asha Rao | | 2 | Ravi Kumar | | 3 | Meena Iyer | | 4 | Kabir Singh | +-------------+-------------+ +-------------+--------------+ | customer_id | name | +-------------+--------------+ | 5 | Priya Sharma | | 6 | Arjun Mehta | | 7 | Divya Nair | | 8 | Rahul Verma | +-------------+--------------+
What this code does
LIMIT 3keeps the first 3 rows of the sorted result, so it gives the 3 most expensive products. WithoutORDER BYit would give 3 rows in no useful order.- Page 2 skips the 4 rows of page 1 with
OFFSET 4. For pagep, the offset is(p - 1) * 4, so page 3 would beOFFSET 8and show the last two customers.
Example 6 · A tie at the LIMIT boundary
-- 5th place is a tie: Karthik Raj and Pooja Gupta both earn 120000.SELECT emp_id, name, salaryFROM employeesORDER BY salary DESC, emp_idLIMIT 5;+--------+-------------+-----------+ | emp_id | name | salary | +--------+-------------+-----------+ | 1 | Vikram Rao | 250000.00 | | 2 | Anita Desai | 180000.00 | | 6 | Neha Joshi | 150000.00 | | 11 | Gita Reddy | 140000.00 | | 3 | Karthik Raj | 120000.00 | +--------+-------------+-----------+
What this code does
- With only
ORDER BY salary DESC, the 5th row could be Karthik or Pooja, because the sort says nothing about their order. The tie-breakeremp_idmakes the answer the same on every run. - Notice that LIMIT still cuts at exactly 5 rows, so Pooja is left out even though she earns the same. If ties must be kept together, "top 5" needs the Subqueries, Views & Window Functions module's
RANK.
LIMIT without ORDER BY returns some rows, not the first ones in any useful sense. For paging, make the order unique with the id as a tie-breaker, or a tied row can show up on two pages or none.
- Top 3:
ORDER BY price DESC LIMIT 3. LIMIT 4, 4is the short form ofLIMIT 4 OFFSET 4: the offset comes first.- Clause order:
SELECT ... FROM ... WHERE ... ORDER BY ... LIMIT .... Any other order is ERROR 1064.
Order of execution
Put the clauses in order
Arrange the clauses to get the 2 dearest accessories.
Next step
Check your understanding
11 quick questions