Now that we know how to use SELECT, FROM, and WHERE in SQL, we can delve deeper into marketing and data analysis. Next, we will sort the results by using another query: ORDER BY

# Table of Contents

Query 1: Using ORDER BY
SELECT film_id, title, rental_rate, rental_duration FROM film WHERE rental_duration=5 ORDER BY rental_rate DESC;

# ORDER BY

ORDER BY is another query that has a literal meaning. It sorts the result in a specified order. Here is a quick definition:

📌ORDER BY: you’re requiring the list to be displayed in a specific order


Q1: How can I sort the film lists by price?

ORDER BY (Sort result by ‘column’)

For our first example, let’s search for movies by film id, title, rental rate, and a rental duration of 5 days. Remember, we’re using the sample database sakila.

🔑 While SELECT * FROM film; is the primary query we went over, this only works if you’re connected to a single database. Let’s say you have two databases, and they both have a table named film. What do you do now?

Easy! Tell SQL that you only want it to look at the film table in the sakila database. You can do this by running the query SELECT * FROM `sakila`.`film`;

✦ Click Run SQL at the top left to try your query!

Whoa, that’s a lot of information. Right now, the factors we want to focus on are film ID, title, rental rate, and rental duration. So let’s tell SQL that we wish to specify the factors by writing the column names after SELECT:

SELECT film_id, title, rental_rate, rental_duration FROM film;

Let’s filter it further by looking at movies with a 5-day rental period.
Plugin WHERE rental_duration=5 at the end of your query.

Wait! Before you run the query, let’s go one more step and ask SQL to change the order of the list. Add ORDER BY and “rental_rate”. You can also tell SQL if you want the list to be in ascending or descending order.

SQL will use ascending by default, but you can use these abbreviations to specify what you wish to quickly.

📌ASC: ascending order
📌DESC: descending order

SELECT film_id, title, rental_rate, rental_duration FROM film WHERE rental_duration=5 ORDER BY rental_rate DESC;

✦ Click Run SQL at the top left to try your query

You can also use the column number instead of the column name. For example, the “rental rate” column is the 3rd column (because we wrote it 3rd in our query). Instead of typing out the full name, we can write ORDER BY 3; to specify the 3rd column.

Try this:

SELECT film_id, title, rental_rate, rental_duration FROM film WHERE rental_duration=5 ORDER BY 3 ASC;

# Practice Time

📰 Your coworker is having problems finding some information about a customer. He asks you for help since you’re a master at navigating databases already. What he needs is a list of all the customer names in the first store location, including their email addresses. He adds that it would be best to have them in alphabetical order by their first name, so it will be easier to sort through them. How do you look up the list?

Here’s the answer. Take a look!

SELECT customer_id, store_id, first_name, last_name, email FROM customer WHERE store_id=1 ORDER BY first_name;

Your results should look similar to the image displayed below:

Don’t forget that an alternative is using the number of the column instead of the name. In this case, the “first name” information is in the 3rd column (because we wrote it 3rd in our query) to use the number 3.

[Order by Column Number] — You’ll get the Same Result

Column Name :
SELECT customer_id, store_id, first_name, last_name, email FROM customer WHERE store_id=1 ORDER BY first_name;

Column Number:
SELECT customer_id, store_id, first_name, last_name, email FROM customer WHERE store_id=1 ORDER BY 3;