ORDER by Command in SQL

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

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