Sort results in SQL

To sort data in SQL, we use the ORDER BY clause .

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.

Syntax

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 :

1. 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] .
2. 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.

For example

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 | +----+----------+-----+-----------+----------+

First, 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