Previous

SQL ORDER BY

The ORDER BY clause is used in SQL to sort the result set based on one or more columns. You can specify ascending or descending order to arrange the data according to your preference.

 

Basic Syntax of ORDER BY

SELECT column1, column2, ...
FROM table_name
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...;
  • column1, column2, ...: Specifies the columns based on which the result should be sorted.
  • ASC: Sorts the result in ascending order (default).
  • DESC: Sorts the result in descending order.

 

Key Features of ORDER BY Clause

1. Sorting in Ascending Order

By default, the ORDER BY clause sorts the result set in ascending order, meaning from the lowest to the highest value (e.g., from A to Z, 1 to 10).

SELECT * FROM employees
ORDER BY salary ASC;

This will return employees sorted by their salary in ascending order (lowest salary first).

2. Sorting in Descending Order

If you want to sort in descending order (highest to lowest), you can use the DESC keyword.

SELECT * FROM employees
ORDER BY salary DESC;

This will return employees sorted by their salary in descending order (highest salary first).

3. Sorting by Multiple Columns

You can sort by multiple columns. The result will first be sorted by the first column, and then, in case of ties, by the second column, and so on.

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

This will return employees sorted by department in ascending order, and within each department, sorted by salary in descending order.

4. Sorting by Column Position

Instead of specifying column names, you can also sort by the column's position in the SELECT statement (1 for the first column, 2 for the second column, etc.).

SELECT first_name, last_name, salary
FROM employees
ORDER BY 3 DESC;

This will sort the result based on the third column (salary) in descending order.

 

Example Queries

1. Sort Employees by Salary in Ascending Order

SELECT * FROM employees
ORDER BY salary ASC;

This query will sort employees in ascending order based on their salary.

2. Sort Employees by Name in Alphabetical Order

SELECT * FROM employees
ORDER BY first_name ASC, last_name ASC;

This will sort employees first by their first name, then by their last name alphabetically.

3. Sort Employees by Department and Salary

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

This query sorts employees by their department alphabetically and, within the same department, sorts them by salary in descending order.

4. Sort Employees by Date of Joining (Newest First)

SELECT * FROM employees
ORDER BY join_date DESC;

This will return employees sorted by their joining date, with the most recent joiners listed first.

5. Sort Employees by ID (Ascending Order)

SELECT * FROM employees
ORDER BY employee_id ASC;

This query will retrieve employees sorted by their employee ID in ascending order.

 

Summary

The ORDER BY clause in SQL is essential for sorting data in a result set. You can sort by one or multiple columns and specify ascending (ASC) or descending (DESC) order. Sorting helps to organize data, making it easier to analyze and present.

Previous