Hack University - ORDER BY: Putting Rows in Order

From lesson 2-1, finding the movies released before 2000 looks like this:

SELECT title FROM movies WHERE year < 2000;

Row filtering is always a WHERE clause, and it always comes after FROM. SQL has no IF inside a SELECT statement, which is a habit worth unlearning early if you are arriving from a general-purpose programming language.

Sorting

So far, rows have come back in the order they were inserted. Never rely on that: without an explicit order, the database is free to return rows however it likes. When order matters, and it usually does (a newest-first feed, a cheapest-first shop page, a leaderboard: each is just a sorted query), say so with ORDER BY:

SELECT title, rating FROM movies ORDER BY rating DESC;

DESC means descending (biggest first).
ASC means ascending (smallest first) and is the default, so ORDER BY year sorts oldest first.
Text columns sort alphabetically.

ORDER BY goes after the WHERE clause if you have one: SELECT ... FROM ... WHERE ... ORDER BY ....

Ranking by rating, highest first

The same five movies from unit 2, now returned best-rated first.

CREATE TABLE movies (
  title TEXT,
  year INTEGER,
  rating REAL,
  genre TEXT
);

INSERT INTO movies VALUES ('Inside Out', 2015, 8.1, 'animation');
INSERT INTO movies VALUES ('The Matrix', 1999, 8.7, 'scifi');
INSERT INTO movies VALUES ('Arrival', 2016, 7.9, 'scifi');
INSERT INTO movies VALUES ('Paddington 2', 2017, 7.8, 'family');
INSERT INTO movies VALUES ('Alien', 1979, 8.5, 'scifi');

SELECT title, rating FROM movies ORDER BY rating DESC;

Output

The Matrix|8.7
Alien|8.5
Inside Out|8.1
Arrival|7.9
Paddington 2|7.8

ORDER BY goes at the end of the query, after any WHERE clause, and DESC asks for descending order. Leaving it off gives ascending order, which is the default, and ASC spells that out explicitly when you want the query to be self-documenting.

All five rows still come back. Sorting rearranges results, it never removes any, which makes it a different tool from WHERE even though both go at the end of the statement.

Sorting by more than one column

List several columns and the database uses the second as a tie-breaker for the first, the third for the second, and so on:

SELECT genre, rating, title FROM movies
ORDER BY genre, rating DESC;

Rows are grouped alphabetically by genre first. Inside each genre, the higher rating comes first. Each column gets its own ASC/DESC.

Sorting text alphabetically

Text sorts alphabetically with the same clause that sorts numbers.

CREATE TABLE movies (
  title TEXT,
  year INTEGER,
  rating REAL,
  genre TEXT
);

INSERT INTO movies VALUES ('Inside Out', 2015, 8.1, 'animation');
INSERT INTO movies VALUES ('The Matrix', 1999, 8.7, 'scifi');
INSERT INTO movies VALUES ('Arrival', 2016, 7.9, 'scifi');
INSERT INTO movies VALUES ('Paddington 2', 2017, 7.8, 'family');
INSERT INTO movies VALUES ('Alien', 1979, 8.5, 'scifi');

SELECT title FROM movies ORDER BY title;

Output

Alien
Arrival
Inside Out
Paddington 2
The Matrix

Alphabetical order is ascending order, and ascending is the default, so a bare ORDER BY title is all it takes.

One caution for later: Alien and Arrival both start with A, and the tie is broken by the second character. With real data you will also meet mixed capitalization, where many databases sort every uppercase letter before every lowercase one, so apple can land after Zebra.

Two sort columns, two directions

ORDER BY accepts a list of columns, and each one carries its own direction. This query groups the output by genre alphabetically and puts the newest movie first inside each genre.

CREATE TABLE movies (
  title TEXT,
  year INTEGER,
  rating REAL,
  genre TEXT
);

INSERT INTO movies VALUES ('Inside Out', 2015, 8.1, 'animation');
INSERT INTO movies VALUES ('The Matrix', 1999, 8.7, 'scifi');
INSERT INTO movies VALUES ('Arrival', 2016, 7.9, 'scifi');
INSERT INTO movies VALUES ('Paddington 2', 2017, 7.8, 'family');
INSERT INTO movies VALUES ('Alien', 1979, 8.5, 'scifi');

SELECT genre, year, title FROM movies
ORDER BY genre, year DESC;

Output

animation|2015|Inside Out
family|2017|Paddington 2
scifi|2016|Arrival
scifi|1999|The Matrix
scifi|1979|Alien

The columns are separated by a comma, and DESC attaches only to year. genre keeps the default ascending order, so writing ORDER BY genre, year DESC gives two different directions in one clause.

The second column only matters when the first one ties. That is exactly what happens in the three sci-fi rows, where genre is identical and year DESC decides the ordering, while the single animation and family rows have nothing to be compared against.