Welcome back! Now that we've covered the basics of SQL, the SELECT statement, and how to filter data using the WHERE clause, it's time to learn how to sort data. Sorting data can be incredibly useful. For instance, whether you're ranking movies by their budgets or arranging them by their IMDb ratings, sorting helps you find and analyze your data more effectively.
The ORDER BY clause in SQL is used to sort the records fetched by the SELECT statement. Instead of getting results in an arbitrary order, you can specify how the data should be sorted. The ORDER BY clause does this by allowing you to define one or more columns to sort by.
The syntax for the ORDER BY clause is straightforward. Here’s what it looks like:
SQL1SELECT column1, column2 2FROM table_name 3ORDER BY column1;
SELECT column1, column2specifies the columns we want to retrieve.FROM table_namespecifies the table from which to retrieve the data.ORDER BY column1specifies the column by which the results should be sorted.
By default, the ORDER BY clause sorts data in ascending order (from lowest to highest). However, you can also sort data in descending order (from highest to lowest) by using the DESC keyword. For clarity, it is recommended to also include the ASC keyword even though ORDER BY sorts in ascending order by default.
Let's sort our dataset by the budget_million_usd column in ascending order.
SQL1SELECT movie_id, budget_million_usd, imdb_rating 2FROM movie_details 3ORDER BY budget_million_usd ASC;
ORDER BY budget_million_usd ASC sorts the retrieved records by the budget_million_usd column in ascending order. This means the results will be arranged starting from the lowest budget to the highest budget. This can be seen in the output:
Plain text1 movie_id | budget_million_usd | imdb_rating 2----------+--------------------+------------- 3 12 | 130 | 7.3 4 1 | 140 | 7.9 5 5 | 140 | 6.9 6 4 | 150 | 7.0 7 2 | 150 | 6.7 8 23 | 160 | 7.5 9 20 | 162 | 7.1 10 14 | 165 | 7.5 11 9 | 170 | 7.7 12 10 | 170 | 8.0 13 8 | 170 | 6.9 14 21 | 175 | 6.9 15 16 | 175 | 7.4 16 17 | 180 | 7.9 17 28 | 180 | 7.8 18 15 | 200 | 7.6 19 18 | 200 | 7.3 20 7 | 200 | 7.2 21 3 | 200 | 7.0 22 24 | 200 | 6.8 23 25 | 200 | 7.6 24 26 | 200 | 6.8 25 29 | 200 | 7.5 26 31 | 200 | 6.2 27 6 | 220 | 8.0 28 11 | 250 | 7.3 29 13 | 250 | 7.8 30 30 | 250 | 7.3 31 33 | 250 | 6.1 32 32 | 250 | 8.1 33 27 | 260 | 8.4 34 19 | 321 | 8.4 35 22 | 356 | 8.4 36(33 rows)
Here we sort the same dataset by the imdb_rating column but in descending order by adding the DESC keyword.
SQL1SELECT movie_id, budget_million_usd, imdb_rating 2FROM movie_details 3ORDER BY imdb_rating DESC;
In the output, we can see the table lists the movies starting from the highest imdb_rating of 8.4 down to 6.1.
Plain text1 movie_id | budget_million_usd | imdb_rating 2----------+--------------------+------------- 3 27 | 260 | 8.4 4 22 | 356 | 8.4 5 19 | 321 | 8.4 6 32 | 250 | 8.1 7 10 | 170 | 8.0 8 6 | 220 | 8.0 9 1 | 140 | 7.9 10 17 | 180 | 7.9 11 13 | 250 | 7.8 12 28 | 180 | 7.8 13 9 | 170 | 7.7 14 25 | 200 | 7.6 15 15 | 200 | 7.6 16 29 | 200 | 7.5 17 23 | 160 | 7.5 18 14 | 165 | 7.5 19 16 | 175 | 7.4 20 12 | 130 | 7.3 21 18 | 200 | 7.3 22 11 | 250 | 7.3 23 30 | 250 | 7.3 24 7 | 200 | 7.2 25 20 | 162 | 7.1 26 4 | 150 | 7.0 27 3 | 200 | 7.0 28 8 | 170 | 6.9 29 5 | 140 | 6.9 30 21 | 175 | 6.9 31 26 | 200 | 6.8 32 24 | 200 | 6.8 33 2 | 150 | 6.7 34 31 | 200 | 6.2 35 33 | 250 | 6.1 36(33 rows)
The ORDER BY clause is not limited to sorting numerical values. It can also sort rows by alphabetical order. The default ordering is alphabetical order (ascending), but using DESC, the output is in reverse alphabetical order. Let's take a look:
SQL1SELECT *
2FROM movies
3ORDER BY movie_name;The output shows the movies starting from "Ant-Man" down to "Thor: The Dark World":
Plain text1 movie_id | movie_name | release_date | phase 2----------+---------------------------------------------+--------------+------- 3 12 | Ant-Man | 2015-07-17 | 2 4 20 | Ant-Man and The Wasp | 2018-07-06 | 3 5 31 | Ant-Man and The Wasp: Quantumania | 2023-02-17 | 5 6 11 | Avengers: Age of Ultron | 2015-05-01 | 2 7 22 | Avengers: Endgame | 2019-04-26 | 3 8 19 | Avengers: Infinity War | 2018-04-27 | 3 9 18 | Black Panther | 2018-02-16 | 3 10 30 | Black Panther: Wakanda Forever | 2022-11-11 | 4 11 24 | Black Widow | 2021-07-09 | 4 12 13 | Captain America: Civil War | 2016-05-06 | 3 13 5 | Captain America: The First Avenger | 2011-07-22 | 1 14 9 | Captain America: The Winter Soldier | 2014-04-04 | 2 15 21 | Captain Marvel | 2019-03-08 | 3 16 14 | Doctor Strange | 2016-11-04 | 3 17 28 | Doctor Strange in the Multiverse of Madness | 2022-05-06 | 4 18 26 | Eternals | 2021-11-05 | 4 19 10 | Guardians of the Galaxy | 2014-08-01 | 2 20 15 | Guardians of the Galaxy Vol. 2 | 2017-05-05 | 3 21 32 | Guardians of the Galaxy Vol. 3 | 2023-05-05 | 5 22 1 | Iron Man | 2008-05-02 | 1 23 3 | Iron Man 2 | 2010-05-07 | 1 24 7 | Iron Man 3 | 2013-05-03 | 2 25 25 | Shang-Chi and the Legend of the Ten Rings | 2021-09-03 | 4 26 23 | Spider-Man: Far From Home | 2019-07-02 | 3 27 16 | Spider-Man: Homecoming | 2017-07-07 | 3 28 27 | Spider-Man: No Way Home | 2021-12-17 | 4 29 6 | The Avengers | 2012-05-04 | 1 30 2 | The Incredible Hulk | 2008-06-13 | 1 31 33 | The Marvels | 2023-11-10 | 5 32 4 | Thor | 2011-05-06 | 1 33 29 | Thor: Love and Thunder | 2022-07-08 | 4 34 17 | Thor: Ragnarok | 2017-11-03 | 3 35 8 | Thor: The Dark World | 2013-11-08 | 2 36(33 rows)
In this lesson, we've learned how to use the ORDER BY clause to sort data, specifying both ascending and descending order. We also walked through a real-world example of sorting movie data by budget, IMDb rating, and name.
As you move forward, try practicing with different columns and combinations to refine your skills. Up next, you'll get the chance to practice these concepts through hands-on exercises.
Good luck, and happy querying!