Sort Results in SQL

This guide covers sort results in SQL with practical context and easy-to-follow details. Use it to understand the subject and apply the information confidently.

ORDER BY is used to sort data in ascending order or in descending order on one or more columns. Some databases sort the default query results in ascending order.

The basic syntax of the ORDER BY clause is used to sort results in ascending or descending order as follows:

SELECT danhsach_cot
FROM ten_bang
[WHERE dieu_kien]
[ORDER BY cot1, cot2, . cotN] [ASC | DESC];

Note :

• If ASC or DESC is not selected in the ORDER BY clause, the result will be sorted by ascending order by default, equivalent to [ORDER BY cot1, cot2, . CotN] [ASC] .
• You can use more than one column in this ORDER BY clause. Note that any column you are using to sort must be in the List_cach.

Suppose the NHANVIEN table has the following records:

 +----+----------+-----+-----------+----------+ | ID | TEN |TUOI | DIACHI | LUONG | +----+----------+-----+-----------+----------+ | 1 | Thanh | 32 | Haiphong | 2000.00 | | 2 | Loan | 25 | Hanoi | 1500.00 | | 3 | Nga | 23 | Hanam | 2000.00 | | 4 | Manh | 25 | Hue | 6500.00 | | 5 | Huy | 27 | Hatinh | 8500.00 | | 6 | Cao | 22 | HCM | 4500.00 | | 7 | Lam | 24 | Haiduong | 10000.00 | +----+----------+-----+-----------+----------+

The following example illustrates how to sort in ascending order for TEN And LUONG.

SQL> SELECT * FROM NHANVIEN
 ORDER BY TEN, LUONG;

The above example returns the result:

 +----+----------+-----+-----------+----------+ | ID | TEN |TUOI | DIACHI | LUONG | +----+----------+-----+-----------+----------+ | 6 | Cao | 22 | HCM | 4500.00 | | 5 | Huy | 27 | Hatinh | 8500.00 | | 7 | Lam | 24 | Haiduong | 10000.00 | | 2 | Loan | 25 | Hanoi | 1500.00 | | 4 | Manh | 25 | Hue | 6500.00 | | 3 | Nga | 23 | Hanam | 2000.00 | | 1 | Thanh | 32 | Haiphong | 2000.00 | +----+----------+-----+-----------+----------+

The following block will sort the results in descending order by TEN field.

SQL> SELECT * FROM NHANVIEN
 ORDER BY TEN DESC;

The result is:

 +----+----------+-----+-----------+----------+ | ID | TEN |TUOI | DIACHI | LUONG | +----+----------+-----+-----------+----------+ | 1 | Thanh | 32 | Haiphong | 2000.00 | | 3 | Nga | 23 | Hanam | 2000.00 | | 4 | Manh | 25 | Hue | 6500.00 | | 2 | Loan | 25 | Hanoi | 1500.00 | | 7 | Lam | 24 | Haiduong | 10000.00 | | 5 | Huy | 27 | Hatinh | 8500.00 | | 6 | Cao | 22 | HCM | 4500.00 | +----+----------+-----+-----------+----------+

To retrieve rows with their own priority order, the SELECT query will look like this:

SQL> SELECT * FROM NHANVIEN
 ORDER BY (CASE DIACHI
 WHEN 'Hanoi' THEN 1
 WHEN 'Haiduong' THEN 2
 WHEN 'HCM' THEN 3
 ELSE 100 END) ASC, DIACHI DESC; 

The result is:

 +----+----------+-----+-----------+----------+ | ID | TEN |TUOI | DIACHI | LUONG | +----+----------+-----+-----------+----------+ | 2 | Loan | 25 | Hanoi | 1500.00 | | 7 | Lam | 24 | Haiduong | 10000.00 | | 6 | Cao | 22 | HCM | 4500.00 | | 1 | Thanh | 32 | Haiphong | 2000.00 | | 3 | Nga | 23 | Hanam | 2000.00 | | 5 | Huy | 27 | Hatinh | 8500.00 | | 4 | Manh | 25 | Hue | 6500.00 | +----+----------+-----+-----------+----------+

Start by we can arrange employees according to DIACHI with their own order, then the remaining DIACHI will be arranged naturally in the reverse order of the alphabet.

In the next section, we will learn about SQL constraints, remember to follow them.

Previous article: DISTINCT keyword in SQL

Next article: Constraints in SQL