SQL ORDER BY Clause

SQL ORDER BY clause is used with the SELECT statement for arranging retrieved data in sorted order. The ORDER BY clause by default sorts the retrieved data in ascending order. To sort the data in descending order DESC keyword is used with ORDER BY clause. This is a very simple SQL Clause. This clause can be used with one or more columns and can be applied to numerical values as well as character values.

Syntax:

  SELECT column_list|* FROM table_name
  ORDER BY ASC | DESC;

Let us consider a table employee with the following five fields and five records.

Example: Sort Data in Ascending Order

ORDER BY clause by default sort the records of a table in ascending order. You can also use ASC keyword to sort the records in ascending order. With the ORDER BY clause, don’t forget to mention a column on the basis of which sorting will be performed. The below SQL query will return the resultant data in ascending order of the salary.

  SELECT * FROM employee 
  ORDER BY salary;
  or
  SELECT * FROM employee 
  ORDER BY salary ASC;
  

Output:

Example: Sort Data in Descending Order

To sort the record in descending order, we use the DESC keyword after ORDER BY column_name. The below SQL query will return the resultant data in descending order of age.

  SELECT * FROM employee 
  ORDER BY age DESC;

Output:

Example: ORDER BY on Multiple Columns

This clause can be applied to one or more columns but the sorting is done according to the first specified column only. In this example, we sort the records in ascending order by name and salary. Similarly, you can sort the result in descending order.

  SELECT * FROM employee
  ORDER BY name, salary;

Output:

Other Uses of ORDER BY clause:

• If you want your resulting record to be ordered randomly instead of in ascending or descending order, you should use the appropriate keywords according to several databases. Read SQL SELECT RANDOM for this.
• If you want to retrieve a particular number of records from the ORDER BY clause, you should use TOP, LIMIT, and ROWNUM keywords according to databases to limit the number of records. Read SQL TOP, LIMIT, ROWNUM for this.

Conclusion

In this article, we discussed the ORDER BY clause to sort the records in a table either in an ascending or descending order using different examples.

---

---