Starting SQL: Sorting records with ORDER BY

3 min readApr 16, 2022

Hello! today let’s take a look at the SQL keyword ORDER BY . We’ll first look at a question that we want to solve, a mental model, then finally some code to answer the original question!

In this multi-part article you will be doing a bit of SQL query writing so please have access to a relational database management system (i.e. PostgreSQL)!

Resources

Copy the following SQL transactions into a .sql file or your terminal logged into your relational database manager and run.

CREATE TABLE genres (
    id SERIAL PRIMARY KEY,
    genre VARCHAR(50)
);INSERT INTO genres (genre) 
VALUES 
    ('Comedy'),
    ('Horror'),
    ('Sci-fi'),
    ('Romance');CREATE TABLE movies (
    id SERIAL PRIMARY KEY,
    title VARCHAR(50),
    genre_id INTEGER REFERENCES genres(id) ON DELETE SET NULL
);INSERT INTO movies (title, genre_id)
VALUES
    ('Big Daddy', 1),
    ('Transformers', 3),
    ('The Grudge', 2),
    ('Star Wars Episode IV', 3),
    ('A Walk to Remember', 4),
    ('Big Hero 6', 1);

We’ll be working with movies and genres today. Note, for simplicities sake, we will assume that a movie only has 1 genre!

Problem:

Let’s say we have an application that uses movie data. We’d like to be able to list all movies but organized by their genre Id’s. If we were to simply run the following code:

SELECT title, genre_id
FROM movies;

Then we might see the following table:

It’s close, we can see the right columns that we need, but if you look at the genre_id column, it’s not in an order that would let us more easily discern which group it belongs to. So what can we do? This is where the ORDER BY keyword comes in.

Syntax

SELECT title, genre_id
FROM movies
ORDER BY genre_id ASC;

The SELECT keyword tells our database which columns we would like to see in the end result.

the FROM keyword tells the database that these columns come from this table.

Finally, our newest keyword, ORDER BY which can be followed by ASC or DESC . If you are wondering, ASC || DESC determine whether the results are sorted by ascending or descending order.

Try it

Try running the following code! you should be able to see it now order the movies by their genre_id .

Conclusion

Hopefully this article has helped you understand how to use ORDER BY in your SQL queries. In this example, we were only able to get data and sort it by one table, but try throwing a JOIN clause and see if you can replace genre_id with the actual genre name! If you need a refresher or resources find my tutorial series on how to use different JOINs !