SQL ORDER BY Clause

🎓 Top 15 Udemy Courses (80-90% Discount): My Udemy Courses - Ramesh Fadatare — All my Udemy courses are real-time and project oriented courses.

▶️ Subscribe to My YouTube Channel (178K+ subscribers): Java Guides on YouTube

▶️ For AI, ChatGPT, Web, Tech, and Generative AI, subscribe to another channel: Ramesh Fadatare on YouTube

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

first_name	last_name	email
Ramesh	Kumar	ramesh.kumar@example.com
Ramesh	Kumar	ramesh.kumar2@example.com
Sita	Patel	sita.patel@example.com
Priya	Sharma	priya.sharma@example.com
Arjun	Singh	arjun.singh@example.com

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

first_name	last_name	email
Arjun	Singh	arjun.singh@example.com
Priya	Sharma	priya.sharma@example.com
Sita	Patel	sita.patel@example.com
Ramesh	Kumar	ramesh.kumar2@example.com
Ramesh	Kumar	ramesh.kumar@example.com

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

first_name	last_name	email	department_id
Arjun	Singh	arjun.singh@example.com	1
Ramesh	Kumar	ramesh.kumar@example.com	1
Priya	Sharma	priya.sharma@example.com	2
Sita	Patel	sita.patel@example.com	2
Ramesh	Kumar	ramesh.kumar2@example.com	3

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

fname	lname	email
Ramesh	Kumar	ramesh.kumar@example.com
Ramesh	Kumar	ramesh.kumar2@example.com
Sita	Patel	sita.patel@example.com
Priya	Sharma	priya.sharma@example.com
Arjun	Singh	arjun.singh@example.com

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

first_name	last_name	email
Sita	Patel	sita.patel@example.com
Ramesh	Kumar	ramesh.kumar@example.com
Ramesh	Kumar	ramesh.kumar2@example.com
Arjun	Singh	arjun.singh@example.com
Priya	Sharma	priya.sharma@example.com

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.