SQL ORDER BY Clause

Introduction

In this chapter, we will learn how to use the ORDER BY clause in SQL to sort the results of a query. The ORDER BY clause is used to sort the result set of a query by one or more columns. This chapter will cover the syntax, usage, and provide examples to help you understand how to use the ORDER BY clause effectively.

What is the ORDER BY Clause?

The ORDER BY clause is used to sort the result set of a query in ascending or descending order based on one or more columns. By default, the ORDER BY clause sorts the data in ascending order. You can specify the sorting order for each column individually.

Syntax for ORDER BY

Basic Syntax

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

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

Example

Assume we have a table named employees:

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

This command retrieves the first_name, last_name, and email columns from the employees table and sorts the results by last_name in ascending order.

Step-by-Step Example

1. Create a Sample Table

First, we will create a sample tables – employees and departments.

CREATE TABLE departments (
department_id INT PRIMARY KEY,
department_name VARCHAR(50)
);

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

2. Insert Sample Data

INSERT INTO departments (department_id, department_name)
VALUES
(1, 'HR'),
(2, 'Finance'),
(3, 'IT');


INSERT INTO employees (first_name, last_name, email, department_id, salary)
VALUES
('Ramesh', 'Kumar', 'ramesh.kumar@example.com', 1, 50000),
('Sita', 'Patel', 'sita.patel@example.com', 2, 60000),
('Arjun', 'Singh', 'arjun.singh@example.com', 1, 55000),
('Priya', 'Sharma', 'priya.sharma@example.com', 2, 62000),
('Ramesh', 'Kumar', 'ramesh.kumar2@example.com', 3, 50000);

3. Sort Results in Ascending Order

To sort the results by last_name in ascending order:

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

Output

4. Sort Results in Descending Order

To sort the results by last_name in descending order:

SELECT first_name, last_name, email
FROM employees
ORDER BY last_name DESC;

Output

5. Sort by Multiple Columns

You can sort the result set by multiple columns. For example, to sort by department_id in ascending order and then by last_name in descending order:

SELECT first_name, last_name, email, department_id
FROM employees
ORDER BY department_id ASC, last_name DESC;

Output

Using ORDER BY with Aliases

If you use column aliases in your SELECT statement, you can also use these aliases in the ORDER BY clause.

Example

SELECT first_name AS fname, last_name AS lname, email
FROM employees
ORDER BY lname ASC;

Output

Using ORDER BY with Expressions

You can also use expressions in the ORDER BY clause.

Example

To sort by the length of the last_name:

SELECT first_name, last_name, email
FROM employees
ORDER BY LENGTH(last_name) ASC;

Output

Conclusion

The ORDER BY clause is used for sorting the results of your SQL queries. This chapter covered the basic syntax, sorting in ascending and descending order, sorting by multiple columns, using aliases, and using expressions in the ORDER BY clause. Understanding how to use the ORDER BY clause effectively will enhance your ability to manage and analyze your database data.

SQL Queries to Practice

CREATE TABLE employees (
	id INT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50),
    email VARCHAR(50) UNIQUE,
	salary DECIMAL(10, 2),
	department VARCHAR(50)
);


INSERT INTO employees 
VALUES
(1, "Ramesh", "Fadatare", "ramesh@gmail.com", 25000, "IT"),
(2, "Ram", "Jadhav", "ram@gmail.com", 20000, "HR"),
(3, "Sita", "Patel", "sita@gmail.com", 15000, "Sales"),
(4, "Arjun", "Singh", "arjun@gmail.com", 20000, "IT"),
(5, "Priya", "Sharma", "priya@gmail.com", 15000, "HR"),
(6, "Umesh", "Fadatare", "umesh@gmail.com", 20000, "IT");

-- Sort Results in Ascending Order
SELECT *
FROM employees
ORDER BY salary ASC;

-- Sort Results in Dscending Order
SELECT *
FROM employees
ORDER BY salary DESC;

-- Sort by Multiple Columns
SELECT *
FROM employees
ORDER BY department ASC, last_name DESC;

-- ORDER By with WHERE
SELECT *
FROM employees
WHERE department = "IT"
ORDER BY salary ASC;