SQL ORDER BY

In SQL, the ORDER BY clause is used to sort the result set of a query in either ascending or descending order based on one or more columns. By default, sorting is done in ascending order. This clause is often used with the SELECT statement to make data more organized and easier to read.

Syntax

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

• column1, column2… → Columns on which sorting will be done.
• ASC → Ascending order (default).
• DESC → Descending order.

Key Points about ORDER BY

1. Default is ASC: If you do not mention ASC or DESC, the results will be sorted in ascending order.
2. Multiple Columns: You can sort by more than one column. The sorting will be applied in the order you specify.
3. Works with Numbers, Text, and Dates: You can sort data of different types.
4. Can be Combined with Other Clauses: Works with WHERE, GROUP BY, and aggregate functions.

Examples

1. Sorting in Ascending Order

SELECT * FROM employees
ORDER BY salary ASC;

• This will list employees starting from the lowest salary to the highest.

2. Sorting in Descending Order

SELECT * FROM employees
ORDER BY salary DESC;

• This will list employees starting from the highest salary to the lowest.

3. Sorting by Multiple Columns

SELECT * FROM employees
ORDER BY department ASC, salary DESC;

• First, employees will be sorted by department in ascending order.
• Then, within each department, salaries will be sorted from highest to lowest.

4. Sorting by Column Position

Instead of column names, you can use the column’s position in the SELECT statement.

SELECT name, salary, department
FROM employees
ORDER BY 2 DESC;

• Here, 2 refers to the second column (salary), sorting it in descending order.

ORDER BY with Numeric, Text, and Date Values

Sorting Text

SELECT * FROM products
ORDER BY product_name ASC;

• This sorts products alphabetically (A–Z).

Sorting Dates

SELECT * FROM orders
ORDER BY order_date DESC;

• This shows the most recent orders first.

Combining ORDER BY with WHERE

SELECT * FROM employees
WHERE department = 'Sales'
ORDER BY hire_date ASC;

• This lists all Sales department employees from the earliest to latest hire date.

Practical Uses of ORDER BY

• Displaying top-selling products.
• Showing latest blog posts first.
• Sorting student marks to find toppers.
• Arranging events by date.
• Listing customers alphabetically.

Performance Tip

Sorting can affect query performance for large datasets. To speed up sorting:

• Use indexes on columns you frequently sort.
• Limit results with LIMIT (or TOP in SQL Server).

Example:

SELECT * FROM employees
ORDER BY salary DESC
LIMIT 5;

• This returns only the top 5 highest-paid employees.

Conclusion

The ORDER BY clause in SQL is essential for organizing and presenting query results in a meaningful way. Whether you want alphabetical lists, latest entries, or highest values first, ORDER BY makes it possible. Mastering its use will make your data more readable and your reports more effective.