In this lesson: Sort by one or more columns and paginate results.
Sorting
SELECT name, mark FROM students
ORDER BY mark DESC; -- highest first
SELECT name FROM students
ORDER BY name ASC; -- A to Z (ASC is the default)Sorting by several columns
SELECT name, country, mark FROM students
ORDER BY country ASC, mark DESC;Group by country alphabetically, and within each country put the highest mark first. The second column only breaks ties in the first.
LIMIT
SELECT name, mark FROM students
ORDER BY mark DESC
LIMIT 3; -- the top three
LIMIT without ORDER BY is unpredictable. The database is free to return rows in any order, so "LIMIT 10" gives you ten arbitrary rows. Always sort first when the order matters.
Paging through results
-- page 1
SELECT name FROM students ORDER BY id LIMIT 10 OFFSET 0;
-- page 2
SELECT name FROM students ORDER BY id LIMIT 10 OFFSET 10;
-- page 3
SELECT name FROM students ORDER BY id LIMIT 10 OFFSET 20;OFFSET skips rows before starting to return them. That is exactly how a "next page" button works.
Sorting and NULLs
NULLs sort together — in MySQL they come first ascending, last descending. If it matters, be explicit:
ORDER BY (mark IS NULL), mark DESC;Putting it together
SELECT name, mark
FROM students
WHERE country = 'Rwanda'
ORDER BY mark DESC
LIMIT 5;The clause order is fixed: SELECT … FROM … WHERE … ORDER BY … LIMIT. Swap them and you get a syntax error.