ORDER BY command in SQL

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

In particular, the ASC command is used to sort up and DESC is used to sort descending.

In this article, we'll show you in detail how to use SQL ORDER BY clauses with syntax and specific examples to make it easier to visualize and capture statements.

Syntax ORDER BY command in SQL

The basic syntax of the ORDER BY command with the SELECT statement will be 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].

Example of ORDER BY in SQL

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 | Hanoi | 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 | Hanoi | 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 | Hanoi | 10000.00 | | 5 | Huy | 27 | Hatinh | 8500.00 | | 6 | Cao | 22 | HCM | 4500.00 | +----+----------+-----+-----------+----------+

In the next section, we will learn about the GROUP BY statement, please remember to follow it.

Previous post: TOP command in SQL

Next lesson: GROUP BY statement in SQL