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