Lesson 8 – Sorting Records using ORDER BY Clause
In the previous lessons, you studied how to create, read, update and delete data in a database. These operations are collectively known as CRUD operations. In this lesson, you will see how to change the order in which the data is selected. For example, you might need to select all the products from the products table where the most expensive products appear at the top. To do so, you can use the ORDER BY clause.
Note: Before executing the following queries you must have records in the products and categories tables as mentioned in lesson 2 and lesson 3. If you do not have records in the products and categories table, go back and insert the records and then execute the following queries.
Let’s see a simple example of the order by clause.
SELECT * FROM products ORDER BY price DESC
To order the data, you first have to select the data using the SELECT clause and then you can use the ORDER BY class followed by the column according to which you want to sort the data. You also need to specify DESC for descending order and ASC for ascending order. By default, the column values are sorted by ascending order. For string columns, the default sorting is in the alphabetical order. The output of the above query is as follows:
Let’s see another example of sorting. Let’s sort the data by in the alphabetical order of names. Execute the following query:
SELECT * FROM products ORDER BY name
The output of the above script is as follows:
You can also specify two columns in the ORDER by clause. The data will be first sorted by the first column, in case if two values are equal in the first column, those two values will then be sorted as per the second column. Let’s sort our data first by “category_id” and then by price to make this clear.
You can see that here in the output, that the first three products have the same value for the “category_id” hence they are sorted by price.
In this lesson, you saw how to sort the data. In the next lesson, you will see how to perform aggregate functions on the data.