ORDER BY
SELECT * FROM students ORDER BY cgpa DESC;
SELECT * FROM students ORDER BY department ASC, cgpa DESC;
SELECT name_en, cgpa * 25 AS pct FROM students ORDER BY pct DESC; -- alias works
SELECT * FROM students ORDER BY 2; -- by column position
Sorting is ascending by default. You can sort by expressions and by custom orders:
-- custom priority order
SELECT * FROM students
ORDER BY FIELD(department, 'CSE', 'EEE', 'BBA'), name_en;
-- NULLs last (MySQL sorts NULL first in ASC)
SELECT * FROM students ORDER BY email IS NULL, email;
-- random sample (fine for small tables only)
SELECT * FROM students ORDER BY RAND() LIMIT 3;
LIMIT and OFFSET
SELECT * FROM students ORDER BY id LIMIT 10; -- page 1
SELECT * FROM students ORDER BY id LIMIT 10 OFFSET 10; -- page 2
SELECT * FROM students ORDER BY id LIMIT 10 OFFSET 20; -- page 3
General formula: OFFSET = (page_number - 1) * page_size.
The OFFSET problem
LIMIT 10 OFFSET 100000 forces MySQL to read and throw away 100,000 rows. Deep pages get slower and slower.
Keyset (cursor) pagination fixes this by remembering the last value seen:
-- first page
SELECT id, name_en FROM students ORDER BY id LIMIT 20;
-- next page: pass the last id from the previous page
SELECT id, name_en FROM students WHERE id > 8420 ORDER BY id LIMIT 20;
This uses the index directly and stays fast at any depth. The trade-off is that you cannot jump to "page 57" — you can only go forward and back.
Top-N per group
-- the highest-CGPA student in each department (MySQL 8+)
SELECT * FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY department ORDER BY cgpa DESC) AS rn
FROM students
) t
WHERE rn = 1;
Counting total rows for a paginator
SELECT COUNT(*) FROM students WHERE department = 'CSE';
Run the count as a separate query. SQL_CALC_FOUND_ROWS is deprecated in MySQL 8 and usually slower than two plain queries.