Heads up! To view this whole video, sign in with your Courses account or enroll in your free 7-day trial. Sign In Enroll
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