Basics of selection I · Topic 9 Free
Sorting: ORDER BY (and LIMIT)
Data model — the tables you will query
How the tables join
| Relationship | Cardinality | Join condition |
|---|---|---|
| students.cohort_id → cohorts.id | many students → one cohort | JOIN cohorts ON students.cohort_id = cohorts.id |
| enrollments.student_id → students.id | many enrollments → one student | JOIN students ON enrollments.student_id = students.id |
| enrollments.course_id → courses.id | many enrollments → one course | JOIN courses ON enrollments.course_id = courses.id |
| job_offers.student_id → students.id | many job offers → one student | JOIN students ON job_offers.student_id = students.id |
In short: each student belongs to one cohort. Students enroll in courses through enrollments, and their job offers land in job_offers.
Without ORDER BY, SQL gives no guarantee about row order. The same query can return rows in a different order tomorrow. If order matters, say so.
ORDER BY
SELECT company, salary_usd
FROM job_offers
ORDER BY salary_usd DESC;ASC— ascending (small → large, A → Z). This is the default.DESC— descending.
ORDER BY always goes after WHERE (if present) and is almost the last clause in a query.
Several sort keys
Rows equal on the first key are sorted by the second, and so on:
SELECT title, category, hours
FROM courses
ORDER BY category ASC, hours DESC;Sorting and NULLs
In Postgres, NULLs come last in ascending order and first in descending. You can control it:
ORDER BY score DESC NULLS LASTLIMIT and OFFSET
LIMIT caps how many rows come back — with ORDER BY it gives you “top N”:
-- 5 newest students
SELECT name, joined_at
FROM students
ORDER BY joined_at DESC
LIMIT 5;OFFSET skips rows before counting — the classic (if imperfect) way to paginate:
LIMIT 10 OFFSET 20; -- rows 21–30LIMIT without ORDER BY returns some N rows — never rely on which ones.
You can also sort by an expression (ORDER BY LENGTH(name)) or by an alias you defined in SELECT.
Practice
A real Postgres database runs in your browser. Solve the tasks, or just experiment.