To retrieve data in ascending order in SQL, we use the ORDER BY clause with the ASC keyword. By default, ORDER BY sorts data in ascending order, so specifying ASC is optional but makes the query more readable.
Syntax: #
filename.sql
SELECT column1, column2, ...
FROM table_name
ORDER BY column1 [ASC], column2 [ASC];
Example: #
Suppose we have an employees table:
| employee_id | name | salary |
|---|---|---|
| 1 | Alice | 50000 |
| 2 | Bob | 60000 |
| 3 | Charlie | 55000 |
To retrieve employees ordered by salary in ascending order:
filename.sql
SELECT name, salary
FROM employees
ORDER BY salary ASC;
Result:
| name | salary |
|---|---|
| Alice | 50000 |
| Charlie | 55000 |
| Bob | 60000 |
Ordering by multiple columns: #
You can sort by multiple columns. For example, sort first by department, then by salary:
filename.sql
SELECT name, department, salary
FROM employees
ORDER BY department ASC, salary ASC;
Real-world scenario: #
I used ORDER BY in a reporting system to display top-performing products or lowest-stock items in ascending order, helping management quickly see items that required attention.
Challenges: #
- Sorting large datasets can impact performance; indexing the columns used in ORDER BY can help.
- When sorting NULL values, behavior may differ by database — some place NULLs first, others last.
Alternatives/Enhancements: #
- Use DESC to sort in descending order.
- Combine ORDER BY with LIMIT to get top-N or bottom-N results:
filename.sql
SELECT name, salary
FROM employees
ORDER BY salary ASC
LIMIT 5;
In summary, the ORDER BY clause with ASC is the standard way to retrieve data in ascending order, making result sets more readable and useful for reporting or analysis.