ORDER BY | BUS 104 – SQL Exam

Still feeling nervous for your exam? Check out the SQL Cram Kit for practice questions that emulate the difficulty that you'll face on your exam. Each question includes a thorough step-by-step explanation where I walk you through the entire problem solving process!

SQL Cram Kit logo

ORDER BY explained

Remember lining up for attendance as a kid in elementary school?

Let's say we've got the following students in a classroom…

Peter, Lila, Alex, and Bella lined up in a classroom

…and that we need them to line up for attendance in alphabetical order.

So, they scramble around until they finally get into proper order.

Alex, Bella, Lila, and Peter in alphabetical order

This is exactly what ORDER BY statements do.

ORDER BY statements are used to sort results based on a field or aggregate function.

How to code an ORDER BY

Here's the template for an ORDER BY statement:

ORDER BY column(s);

Let's code one with the example above.

Below is the table of students in the classroom.

students
idnameshirt_color
1PeterRed
2LilaBlue
3AlexWhite
4BellaBlue

Let's set up a SELECT and FROM statement to start our query.

SELECT column(s)
FROM database_name.table_name;

What's going to go in the column(s)? We're just going to place "*".

Why? Because we want to see each of the fields in the students table.

SELECT *
FROM database_name.table_name;

For our FROM statement's database_name, let's just say the database is called school. We already know the table_name is students.

SELECT *
FROM school.students;

When we execute this query, we get the following results.

Results
idnameshirt_color
1PeterRed
2LilaBlue
3AlexWhite
4BellaBlue

Notice how the students above are in the exact same order as they are in classroom currently before scrambling into order.

Peter, Lila, Alex, and Bella lined up in a classroom

Now, we can implement our ORDER BY statement.

SELECT *
FROM school.students
ORDER BY column(s);

What's going to go in our column(s)?

Well, for now, all we need to do is have the students be listed in alphabetical order, like so:

Alex, Bella, Lila, and Peter in alphabetical order

So, we'll place the name field as our columns.

SELECT *
FROM school.students
ORDER BY name;

When we execute this, we get the following results:

Results
idnameshirt_color
3AlexWhite
4BellaBlue
2LilaBlue
1PeterRed

This matches what we were wanting. The students are now in alphabetical order by name!

What if we wanted the students to line up in reverse alphabetical order by name, like so?

Peter, Lila, Bella, and Alex in reverse alphabetical order

We'd use the "DESC" keyword after name!

SELECT *
FROM school.students
ORDER BY name DESC;

The DESC keyword means descending. It sorts our results by name in descending, or reverse, order.

When executing this query, we get the following results.

Results
idnameshirt_color
1PeterRed
2LilaBlue
4BellaBlue
3AlexWhite

Perfect, now students are sorted in reverse alphabetical order!

Using ORDER BY with multiple columns

With ORDER BY statements, you're able to place multiple fields in ORDER BY statements. Let's try sorting by shirt_color as well.

We want our query to first sort by name in descending order, then sort by shirt_color.

So, we'll place shirt_color after "name DESC".

SELECT *
FROM school.students
ORDER BY name DESC, shirt_color;

And when we execute this, we get the following results:

Results
idnameshirt_color
2LilaBlue
4BellaBlue
1PeterRed
3AlexWhite

So, how'd we get these results?

Because first, we sorted by name in descending order, and then we sorted by shirt_color.

That's why the shirt_color are the main field in alphabetical order, because they were sorted last.

If this is still confusing to you, notice how Lila and Bella alone are still in reverse alphabetical order. This is because the students were first sorted by name in reverse alphabetical order, and then Lila and Bella were brought to the top because their shirt_color (Blue) was first alphabetically.

Using ORDER BY with numeric values

We've only been using ORDER BY with string values so far. But, you can use it with numeric values as well.

For example, if we wanted to sort our students by the id field, we could do the following:

SELECT *
FROM school.students
ORDER BY id;

And when we run this query, we'd get:

Results
idnameshirt_color
1PeterRed
2LilaBlue
3AlexWhite
4BellaBlue

If we wanted to, we could also sort them in descending order by id. To do so, we'd place DESC after "id"…

SELECT *
FROM school.students
ORDER BY id DESC;

…and when we'd run the query, we'd get the following results:

Results
idnameshirt_color
4BellaBlue
3AlexWhite
2LilaBlue
1PeterRed

See? Now the students are sorted in descending order by id.

SQL Follow-along Guide

It's no secret you retain info better when you write it down. That's why I created the SQL Follow-along Guide for you!

As you come upon key concepts highlighted in yellow, like this sentence here, you can fill-in-the-blanks on your Follow-along Guide so that you remember all the important stuff for later.

You can obtain the SQL Follow-along Guide for FREE by entering your email below!

Now that we know how to sort our results, let's move onto Troubleshooting errors and go over a couple common issues you might run into while coding. That way, you're prepared to face them on your exam!

Concepts

Each exam concept broken down with relatable situations to your life!

ToolsSQL Follow-along Guide (FREE)
LessonWhat is SQL?
LessonSELECT & FROM
LessonJOIN
LessonWHERE
LessonGROUP BY
LessonHAVING
LessonORDER BY
LessonTroubleshooting errors

Practice Problems

Include step-by-step explanations through each part of the problem!

SQL Cram Kit Logo

"The SQL Cram Kit was the best thing I could’ve done before my exam. It takes practice and more practice to be good at coding and that is what I needed."

Andrew Green
October 29, 2020

Rating: 5 out of 5.

Leave a Comment