Heads up! To view this whole video, sign in with your Courses account or enroll in your free 7-day trial. Sign In Enroll
Preview
Start a free Courses trial
to watch this video
In this video we're going to take a look at ordering results, that is, retrieving data in a specific order.
Ordering by a single column criteria:
SELECT * FROM <table name> ORDER BY <column> [ASC|DESC];
ASC is used to order results in ascending order.
DESC is used to order results in descending order.
Examples:
SELECT * FROM books ORDER BY title ASC;
SELECT * FROM products WHERE name = "Sonic T-Shirt" ORDER BY stock_count DESC;
SELECT * FROM users ORDER BY signed_up_on DESC;
SELECT * FROM countries ORDER BY population DESC;
Ordering by multiple column criteria:
SELECT * FROM <table name> ORDER BY <column> [ASC|DESC],
<column 2> [ASC|DESC],
...,
<column n> [ASC|DESC];
Ordering is prioritized left to right.
Examples:
SELECT * FROM books ORDER BY genre ASC,
title ASC;
SELECT * FROM books ORDER BY genre ASC,
year_published DESC;
SELECT * FROM users ORDER BY last_name ASC,
first_name ASC;
Cheat Sheets
First, we're going to take
a look at ordering results.
0:00
That is,
retrieving data in a specific order.
0:03
Here's some examples of why you'd want
to retrieve data in a specific order.
0:08
You may want to sort a phone book
alphabetically by last name, and
0:12
then first name.
0:16
Imagine you were running an online movie
streaming service like Netflix, and
0:18
the movies you had to offer
changed from month to month.
0:22
Your website may have
an option to sort your movies
0:25
by the year they were released.
0:29
Some users may want to watch classics,
or maybe movies from this year.
0:31
Having the ability to order by the oldest
first and the newest first is needed.
0:36
You may want to allow users of
a website to sort products by price.
0:42
From the lowest to highest,
or highest to lowest.
0:46
On news sites and blogs, articles tend
to be ordered by their publication date,
0:50
the newest articles appearing first.
0:54
With SQL, you can order your data on text,
numeric, and date types.
0:57
But before you can order your results
you first need to retrieve some results.
1:02
As with any SQL query,
we need to start with a select statement.
1:07
To order by a particular column you
need to add the keywords ORDER and
1:12
BY followed by the column name.
1:16
This will order values in
a column in ascending order.
1:19
From the lowest number
to the highest number.
1:23
For text strings,
it sorts alphabetically, A to Z, and for
1:26
dates, it goes from the earliest
to the most recent.
1:30
You can specify if you
want to be ascending or
1:33
descending by using a keyword
at the end of the statement.
1:36
To descend values from larger numbers
to the lower numbers, from Z-A for
1:41
text values and for
most recent days use DESC.
1:46
Let's see this in action in Treehouse's
interactive SQL programming
1:53
environment SQL Playground.
1:57
Click on the Launch SQL Playground
button associated with this video.
1:59
We're in a database for an online store.
2:04
We want to generate a report
called the Product Stock Counts.
2:07
You can familiarize yourself with the
database schema down here if you want, but
2:11
right now we're going to
focus on the products table.
2:15
You can see there is a stock_count.
2:21
We can order by this.
2:24
>> We can see the stock count
is going up or ascending.
2:30
This is the same query as adding
the ASC keyword at the end.
2:36
When you run this query
the results are the same.
2:44
What if we wanted to run a report to find
the products with the most units in stock?
2:48
For example, we might want to
find overstocked products or
2:53
the ones that aren't selling well.
2:57
Running this query with DESC at the end
will help begin that discussion.
2:59
Let's hop into the customer's table.
3:07
We want to order the customers
alphabetically by their last name and
3:11
then by their first name.
3:15
We can start by writing
SELECT * FROM customers.
3:17
Order by last_name.
3:26
Let's add ASC so we're being more
explicit in our query, and run.
3:33
But how do we order by the first name
as a secondary ordering criteria?
3:39
After the first ordering criteria,
add a comma
3:44
followed by the column name, and
if you want it ascending or descending.
3:48
This query will then order by last name,
and then the first name.
3:54
Let's see it in action.
3:59
As you can see,
it's ordering like we expect.
4:00
You can order by as many columns and
in any combination of ascending and
4:04
descending orders.
4:08
Watch what happens when I add DESC
to the last name's ordering clause.
4:10
>> Now,
the last name is in descending order and
4:17
the first name is still
in alphabetical order.
4:20
Let's change it back from
descending to ascending, and
4:24
then change the first name's
ordering to descending.
4:27
>> What do we see here?
4:36
The last names are in alphabetical order
and the first names are in reverse order.
4:38
To recap, you can order by a single
column by using the order by
4:44
keywords followed by a column
you'd like to order by and
4:49
then an optional keyword to specify
the direction, ascending or descending.
4:53
To order by multiple columns,
5:00
state each column ordering
criteria separated by commas.
5:01
The order is important.
5:06
It's sorted first on the first column
mentioned, the second second, and so on.
5:08
You need to sign up for Treehouse in order to download course files.
Sign up