Lesson 8 — Sorting Results

ORDER BY

ORDER BY controls the sequence of rows in your results. Without it, the order is not guaranteed — databases can return rows in any order unless you specify.

SQL — Basic ORDER BY 
-- Sort ascending (default):
SELECT full_name, mark
FROM   learners
ORDER  BY mark;           -- ASC is implied

-- Sort descending:
SELECT full_name, mark
FROM   learners
ORDER  BY mark DESC;

-- Sort by text:
SELECT full_name, city
FROM   learners
ORDER  BY full_name ASC;  -- alphabetical
SQL — Multi-column and alias sort 
-- Multiple sort columns — sorts by first, then by second for ties:
SELECT full_name, course, mark
FROM   learners
ORDER  BY course ASC, mark DESC;
-- Groups by course alphabetically, then within each course: highest mark first

-- Sort by column position (fragile — avoid in production):
SELECT full_name, city, mark
FROM   learners
ORDER  BY 3 DESC;   -- 3rd column = mark

-- Sort by alias:
SELECT full_name, mark * 0.9 AS adjusted_mark
FROM   learners
ORDER  BY adjusted_mark DESC;
SQL — NULLs in ORDER BY 
-- Sorting NULLs:
-- MySQL / SQL Server: NULLs sort first in ASC, last in DESC (default)
-- PostgreSQL: opposite — NULLs sort last in ASC, first in DESC

-- Explicit NULL handling (PostgreSQL / SQLite):
SELECT full_name, mark
FROM   learners
ORDER  BY mark DESC NULLS LAST;  -- NULLs always at the end

-- Practical example — top 5 passes, then ungraded:
SELECT full_name, COALESCE(mark, 0) AS mark
FROM   learners
ORDER  BY mark DESC
LIMIT  5;
SQL — Custom sort order 
-- CASE in ORDER BY — custom sort order:
SELECT full_name, course
FROM   learners
ORDER  BY
    CASE course
        WHEN 'Java'   THEN 1
        WHEN 'Python' THEN 2
        WHEN 'SQL'    THEN 3
        WHEN 'C#'     THEN 4
        ELSE 5
    END,
    full_name ASC;  -- alphabetical within each course

Practice Task

Your Turn

Write queries: (1) all learners sorted by city then by full_name within each city; (2) top 10 marks (mark DESC, full_name ASC for ties); (3) courses sorted alphabetically, with a second query that uses CASE to put your preferred course first; (4) all learners ordered by mark ascending with NULLs at the end.

Common Mistakes

  • Relying on default row order — without ORDER BY, results come back in no guaranteed order.
  • ORDER BY column number breaks when the SELECT list changes — use column names.
  • ORDER BY on a non-selected column is allowed but confusing in DISTINCT queries.
  • Sorting text columns numerically — '10' sorts before '9' alphabetically. Cast if you need numeric sort.

Professional Tip

In production SQL, always specify ORDER BY when the order of results matters to the application. Never assume the database will return rows in the same order twice without it.

Mini Quiz

What is the default sort direction when ASC or DESC is omitted?