sql
  1. sql-order-by-multiple-cols
PreviousNext

ORDER BY Clause - ORDER BY Multiple Cols

The ORDER BY clause is used to sort the result set returned by a SELECT statement. In SQL, ORDER BY clause can be used to sort the result set either in ascending order (default) or in descending order.

Sometimes, it is required to order the result set by more than one column. In that case, ORDER BY clause with multiple columns can be used.

Syntax

The syntax for ORDER BY clause with multiple columns is:

SELECT column1, column2, ...
FROM table_name
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ... ;

Example

Consider an example table employees that contains columns id, name, department, and salary. To sort this table by department in ascending order and salary in descending order, we can use the following SQL query:

SELECT id, name, department, salary
FROM employees
ORDER BY department ASC, salary DESC;

Output

The query will return the id, name, department, and salary of all employees, sorted by department in ascending order and salary in descending order.

id    name    department    salary
-----------------------------------
3     John    Accounting    95000
2     Mary    HR            75000
4     Jen     Marketing    80000
1     Bob     IT            60000
5     Tom     IT            50000

Explanation

In the ORDER BY clause, we have specified two columns: department and salary. The sorting order for each column is specified using the ASC or DESC keyword. In this case, we want to sort department in ascending order (alphabetical order) and salary in descending order (highest to lowest).

The result set is sorted first by department. If two employees belong to the same department, then they are further sorted based on their salary.

Use

The ORDER BY clause with multiple columns can be used in various scenarios, such as:

  • Sorting a list of products by price and name
  • Sorting a list of students by grade and name
  • Sorting a list of sales by date and amount

Important Points

Here are some important points to remember while using ORDER BY clause with multiple columns:

  • The columns used in the ORDER BY clause must be present in the SELECT statement.
  • The sorting order can be different for each column. The default order is ascending.
  • If two rows have the same value for the first column, then they are sorted based on the second column.
  • The ORDER BY clause should be the last clause in the SELECT statement.

Summary

In SQL, ORDER BY clause is used to sort the result set. When we want to sort the result set by more than one column, we can use ORDER BY clause with multiple columns. The syntax is straightforward and the sorting order can be different for each column. It is a useful feature to have when we want to sort data with more complex requirements.

ORDER BY LIMITINSERT Statement
Published on: