Sorting Data (ORDER BY)

The ORDER BY Function

The ORDER BY clause is used to sort query results by one or more columns. By default, sorting is done in ascending order.

Basic Syntax

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

ASC (Ascending)

Sorts from the smallest to the largest value. This is the default.

SELECT NAME, SALARY FROM EMPLOYEE
ORDER BY SALARY ASC;

DESC (Descending)

Sorts from the largest to the smallest value.

SELECT NAME, SALARY FROM EMPLOYEE
ORDER BY SALARY DESC;

ORDER BY with Multiple Columns

If values in the first column are equal, the sort continues on the second column.

SELECT NAME, DEPARTMENT, SALARY
FROM EMPLOYEE
ORDER BY DEPARTMENT ASC, SALARY DESC;

Employees are sorted by department (A-Z), then by salary (largest to smallest) within the same department.

ORDER BY with Column Numbers

You can use the column position number as a shortcut:

SELECT NAME, SALARY, DEPARTMENT
FROM EMPLOYEE
ORDER BY 2 DESC;
-- Column 2 is SALARY

ORDER BY with Expressions

Sorting can also be done based on expressions:

-- Sort by name length
SELECT NAME FROM EMPLOYEE
ORDER BY LENGTH(NAME) DESC;

-- Sort by salary after bonus
SELECT NAME, SALARY
FROM EMPLOYEE
ORDER BY SALARY * 1.10 DESC;

Complete Example

The EMPLOYEE table:

-- All employees sorted by highest salary
SELECT NAME, SALARY FROM EMPLOYEE
ORDER BY SALARY DESC;
-- Result: Mike (7M), Eric (7M), Dana (6M), Anna (5.5M), John (4.5M)

-- Employees sorted by department, then by salary
SELECT NAME, DEPARTMENT, SALARY FROM EMPLOYEE
ORDER BY DEPARTMENT, SALARY DESC;
-- HR: Dana (6M), John (4.5M)
-- IT: Mike (7M), Eric (7M), Anna (5.5M)

ORDER BY with NULL

The behavior of NULL during sorting depends on the DBMS:

-- PostgreSQL: NULL is treated as the largest value with DESC
-- MySQL: NULL is treated as the smallest value with ASC
SELECT NAME, PHONE FROM EMPLOYEE
ORDER BY PHONE ASC;

Usage Tips

1. Always specify ASC or DESC so the results are consistent
2. Use indexes on frequently sorted columns for better performance
3. Combine with LIMIT to get top-N records

After understanding data sorting, the next step is removing duplicate data using DISTINCT.