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 position
NULL placement
Postgres sorts NULL last in ASC and first in DESC by default — the opposite of MySQL. Control it explicitly:
SELECT * FROM students ORDER BY email ASC NULLS FIRST;
SELECT * FROM students ORDER BY cgpa DESC NULLS LAST;
Custom and collated ordering
-- custom priority
SELECT * FROM students
ORDER BY array_position(ARRAY['CSE','EEE','BBA'], department), name_en;
-- case-insensitive / language-aware sorting
SELECT * FROM students ORDER BY name_en COLLATE "en_US";
SELECT * FROM students ORDER BY lower(name_en);
-- random sample (fine only for small tables)
SELECT * FROM students ORDER BY random() LIMIT 3;
-- efficient random sample of a large table
SELECT * FROM students TABLESAMPLE SYSTEM (1);
Pagination with OFFSET
SELECT * FROM students ORDER BY id LIMIT 10 OFFSET 0; -- page 1
SELECT * FROM students ORDER BY id LIMIT 10 OFFSET 10; -- page 2
OFFSET = (page - 1) * page_size. This is simple and fine for the first few pages.
Why deep OFFSET is slow
OFFSET 100000 makes Postgres read and discard 100,000 rows before returning anything. Every page gets slower than the last.
Keyset pagination remembers the last row seen:
-- first page
SELECT id, name_en FROM students ORDER BY id LIMIT 20;
-- next page
SELECT id, name_en FROM students WHERE id > 8420 ORDER BY id LIMIT 20;
With a compound sort, compare tuples — Postgres supports row comparison directly, which maps perfectly to a multi-column index:
SELECT * FROM students
WHERE (cgpa, id) < (3.50, 8420)
ORDER BY cgpa DESC, id DESC
LIMIT 20;
The cost is that you can only move forward and back, not jump to page 57.
Counting rows for a paginator
SELECT count(*) FROM students WHERE department = 'CSE';
-- fast estimate for huge tables
SELECT reltuples::bigint AS estimate
FROM pg_class WHERE relname = 'students';
An exact count(*) on a large Postgres table always scans, because MVCC means visibility differs per transaction. If the count is just for a "showing 1–20 of N" label, an estimate is usually the better engineering trade.
Top-N per group
SELECT DISTINCT ON (department) department, name_en, cgpa
FROM students
ORDER BY department, cgpa DESC;
For more than one row per group, use row_number() in a CTE — covered in the window functions page.