SQL ORDER BY

SQL ORDER BY Statement

The SQL ORDER BY statement is used to sort the result set of a query by one or more columns. This statement allows you to order the data in ascending or descending order.

---

Syntax

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

The ORDER BY statement has the following components:

• column1, column2, ...: The columns by which to sort the result set.
• table_name: The name of the table from which to retrieve the data.
• ASC: Sorts the result set in ascending order (default).
• DESC: Sorts the result set in descending order.

---

Example SQL ORDER BY Statement Queries

Let's look at some examples of SQL ORDER BY statement queries:

1. Basic ORDER BY Example

SELECT first_name, last_name
FROM employees
ORDER BY last_name ASC;

This query retrieves the first_name and last_name columns from the employees table and sorts the result set by last_name in ascending order. The result will be a list of employees sorted by last name.

2. ORDER BY Multiple Columns

SELECT first_name, last_name, hire_date
FROM employees
ORDER BY last_name ASC, hire_date DESC;

This query retrieves the first_name, last_name, and hire_date columns from the employees table and sorts the result set by last_name in ascending order and then by hire_date in descending order. The result will be a list of employees sorted first by last name and then by hire date.

3. ORDER BY with a Condition

SELECT first_name, last_name, salary
FROM employees
WHERE salary > 50000
ORDER BY salary DESC;

This query retrieves the first_name, last_name, and salary columns from the employees table where the salary is greater than 50000 and sorts the result set by salary in descending order. The result will be a list of employees with salaries over 50000, sorted by salary.

---

Full Example

Let's go through a complete example that includes creating a table, inserting data, and querying the table with the ORDER BY statement.

Step 1: Creating a Table

This step involves creating a new table named employees to store employee data.

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    hire_date DATE,
    salary DECIMAL(10, 2)
);

In this example, we create a table named employees with columns for id, first_name, last_name, hire_date, and salary.

Step 2: Inserting Data into the Table

This step involves inserting some sample data into the employees table.

INSERT INTO employees (first_name, last_name, hire_date, salary)
VALUES ('John', 'Doe', '2020-01-15', 60000.00);

INSERT INTO employees (first_name, last_name, hire_date, salary)
VALUES ('Jane', 'Smith', '2019-03-22', 55000.00);

INSERT INTO employees (first_name, last_name, hire_date, salary)
VALUES ('Jim', 'Brown', '2021-07-30', 70000.00);

Here, we insert data into the employees table.

Step 3: Querying the Table with ORDER BY

This step involves selecting the data from the employees table and sorting the result set using the ORDER BY statement.

SELECT first_name, last_name, hire_date, salary
FROM employees
ORDER BY last_name ASC, hire_date DESC;

This query retrieves all the rows from the employees table and sorts them by last_name in ascending order and hire_date in descending order. The result will be:

first_name  last_name  hire_date   salary
----------- ---------- ----------  --------
Jim         Brown      2021-07-30  70000.00
John        Doe        2020-01-15  60000.00
Jane        Smith      2019-03-22  55000.00

Conclusion

The SQL ORDER BY statement is a powerful tool for sorting the result set of a query by one or more columns. Understanding how to use the ORDER BY statement and its syntax is essential for effective data management and analysis in SQL databases.