Sorting ​

When we use SELECT queries, the results are often sorted by the id (the primary key), which is common in most databases. If we want to sort based on other criteria, we can add the ORDER BY clause. For example, to sort by score from lowest to highest:

-- Sort by score in ascending order:
SELECT id, name, gender, score FROM students ORDER BY score;

To sort in descending order (from highest to lowest), we can add DESC:

-- Sort by score in descending order:
SELECT id, name, gender, score FROM students ORDER BY score DESC;

If there are identical values in the score column, we can add another column for further sorting. For example, using ORDER BY score DESC, gender will sort first by score in descending order and then by gender:

-- Sort by score and then by gender:
SELECT id, name, gender, score FROM students ORDER BY score DESC, gender;

The default sorting order is ascending (ASC), which can be omitted, so ORDER BY score ASC has the same effect as just ORDER BY score.

If there is a WHERE clause, the ORDER BY clause should come after it. For instance, to query the scores of students in class 1 and sort them in descending order:

-- ORDER BY with a WHERE condition:
SELECT id, name, gender, score
FROM students
WHERE class_id = 1
ORDER BY score DESC;

In this case, the result set includes only records that meet the WHERE condition and sorts them as specified by ORDER BY.

Summary ​

The ORDER BY clause allows us to sort the result set. We can sort by multiple columns in either ascending or descending order.