How to Use ORDER BY in SQL

In this SQL section, you will learn what the ORDER BY keyword is and how to use it in SQL. We will also discuss how to use the ORDER BY clause with the WHERE clause.

What is ORDER BY?

ORDER BY is a clause in SQL which shows the result-set of the SELECT statement in either ascending or descending order.

This clause is always used with the SELECT query in Structured Query Language.

Syntax of ORDER BY clause

SELECT Column_Name_1, Column_Name_2, ........, Column_Name_N FROM Table_Name ORDER BY Column_Name_1, Column_Name_2, ........, Column_Name_N;

We can use more than one table field in the ORDER BY clause. We have to separate the name of multiple columns by a comma.

Note: If you have not defined the ascending or descending order in query, SQL automatically shows the result in ascending order.

If you want to know how to use the ORDER BY clause in the SQL table, you have to follow the following points:

Create a Simple Database and Table.
Insert Data into the Table.
View the Inserted Data without ORDER BY clause.
Use the ORDER BY clause.

Step 1: Create the simple Database and Table

First, you have to make a new database in SQL.

The following query creates the Hospital Database:

CREATE Database Hospital;

Now, you have to create the new table using the following CREATE TABLE syntax:

CREATE TABLE table_name
(
column_Name_1 data type (size of the column_1),
column_Name_2 data type (size of the column_2),
column_Name_3 data type (size of the column_3),
...
column_Name_N data type (size of the column_1)
);

The following query creates the Patient table in the Hospital Database:

CREATE TABLE Patient
(
Patient_ID Int PRIMARY KEY,
Patient_Name VARCHAR (100),
Patient_Disease VARCHAR (80),
Patient_Age Int NOT NULL,
Patient_City Int NOT NULL Varchar (70)
) ;

Step 2: Insert the Data into the table

Now, you have to insert the data in the table using the following syntax:

INSERT INTO (Column_Name_1, Column_Name_2, Column_Name_3, ......., Column_Name_N) VALUES (value_1, value_2, value_3, ...., value_N);

The following query inserts the record of admitted patients in the Patient table of the Hospital database:

INSERT INTO Patient (Patient_ID, Patient_Name, Patient_Disease, Patient_Age, Patient_City) VALUES ( 1035, Jones, Malaria, 25, Goa),
(1015, Marry, Diabetes, 44, Mumbai),
(1003, Harry, Fever, 29, Kochi),
(1044, Ella, Cancer, 40, Hyderabad),
(1025, Moria, Corona, 52, Kolkata);

Step 3: View the Inserted data of Table without ORDER BY clause

The following query shows the record of Patients in an unsorted manner:

SELECT * FROM Patient;

The output of the above SELECT query is shown below:

Patient_ID	Patient_Name	Patient_Disease	Patient_Age	Patient_City
1035	Jones	Malaria	25	Goa
1015	Marry	Diabetes	44	Mumbai
1003	Harry	Fever	29	Kochi
1044	Ella	Cancer	40	Hyderabad
1025	Moria	Corona	52	Kolkata

Step 4: Use the ORDER BY clause

The following query uses ODDER BY clause to show all records of patients by Patient_ID from minimum to maximum:

SELECT * FROM Patient ORDER BY Patient_ID;

The output of the above SELECT with ODER BY query is shown in the following Patient table:

Patient_ID	Patient_Name	Patient_Disease	Patient_Age	Patient_City
1003	Harry	Fever	29	Kochi
1015	Marry	Diabetes	44	Mumbai
1025	Moria	Corona	52	Kolkata
1035	Jones	Malaria	25	Goa
1044	Ella	Cancer	40	Hyderabad

The following query uses ODDER BY clause with more than one column of the Patient table:

SELECT Patient_ID, Patient_Name, Patient_Age FROM Patient ORDER BY Patient_Name, Patient_Age;

First, this query sorts the records of patients by Patient_Name, and if Patient_Name repeats more than once, it sorts those rows by Pateint_Age.

Output:

How to Use ORDER BY in SQL

ORDER BY ASC

The ASC is a keyword used with the ORDER BY clause to sort the selected row in ascending order.

Syntax of ORDER BY ASC

SELECT Column_Name_1, Column_Name_2, ........, Column_Name_N FROM Table_Name ORDER BY Column_Name_1 ASC, Column_Name_2 ASC, ........, Column_Name_N ASC;

Example of ORDER BY ASC

The following query creates the new Department table in the School database.

CREATE TABLE Department
(
Department_ID INT PRIMARY KEY,
Department_Name VARCHAR (50),
Department_Leader VARCHAR (70)
) ;

The following INSERT INTO query inserts the records into the Department table:

INSERT INTO Department(Department_ID, Department_Name, Department_Leader) VALUES (2211, Teacher, Ramesh),
(2252, Library, Somya),
(2201, Transport, Suresh),
(2224, Office, Aman),
(2248, Account, Bhanu),
(2208, Security, Sonu),
(2221, Peon, Punit ));

The following query shows the record of the above table:

SELECT * FROM Department;

Output:

Department_ID	Department_Name	Department_Leader
2211	Teacher	Ramesh
2252	Library	Somya
2201	Transport	Suresh
2224	Office	Aman
2248	Account	Bhanu
2208	Security	Sonu
2221	Peon	Punit

The following query shows the selected records of the department table in descending order by Department_ID:

SELECT * FROM Department ORDER BY Department_ID ASC;

Output:

Department_ID	Department_Name	Department_Leader
2201	Transport	Suresh
2208	Security	Sonu
2211	Teacher	Ramesh
2221	Peon	Punit
2224	Office	Aman
2248	Account	Bhanu
2252	Library	Somya

ORDER BY DESC

The DESC is a keyword used with the ORDER BY clause to sort the selected rows in a descending order, i.e., from high to low.

Syntax of ORDER BY DESC

SELECT Column_Name_1, Column_Name_2, ........, Column_Name_N FROM Table_Name ORDER BY Column_Name_1 DESC, Column_Name_2 DESC, ........, Column_Name_N DESC;

Example of ORDER BY DESC

The following query creates the new Department table in the School database:

CREATE TABLE Department
(
Department_ID INT PRIMARY KEY,
Department_Name VARCHAR (50),
Department_Leader VARCHAR (70)
) ;

The following query inserts the given record into the Department table:

The following query shows the record of the above table:

SELECT * FROM Department;

Output:

Department_ID	Department_Name	Department_Leader
2211	Teacher	Ramesh
2252	Library	Somya
2201	Transport	Suresh
2224	Office	Aman
2248	Account	Bhanu
2208	Security	Sonu
2221	Peon	Punit

The following query shows the selected records of the department table in descending order by Department_ID:

SELECT * FROM Department ORDER BY Department_ID DESC;

Output:

Department_ID	Department_Name	Department_Leader
2252	Library	Somya
2248	Account	Bhanu
2224	Office	Aman
2221	Peon	Punit
2211	Teacher	Ramesh
2208	Security	Sonu
2201	Transport	Suresh

ORDER BY with WHERE Clause

The ORDER BY keyword can also be used with the WHERE clause in the SELECT query of Structured Query Language.

Syntax of ORDER BY with WHERE Clause

SELECT Column_Name_1, Column_Name_2, ........, Column_Name_N FROM Table_Name WHERE [condition] ORDER BY Column_Name [ASC | DESC];

Example of ORDER BY with WHERE Clause

The following query creates the Patient_Details table in the Hospital Database:

CREATE TABLE Patient_Details
(
Patient_ID Int PRIMARY KEY,
Patient_Name VARCHAR (100),
Patient_Disease VARCHAR (80),
Patient_Age Int NOT NULL,
Patient_City Int NOT NULL Varchar (70)
) ;

The following query inserts the record of admitted patients in the Patient_Details table of the Hospital database:

INSERT INTO Patient_Details (Patient_ID, Patient_Name, Patient_Disease, Patient_Age, Patient_City) VALUES ( 1035, Jones, Malaria, 25, Goa),
(1015, Marry, Diabetes, 44, Mumbai),
(1003, Harry, Fever, 29, Kochi),
(1044, Ella, Cancer, 40, Hyderabad),
(1025, Moria, Corona, 52, Kolkata);

The following query shows the record of Patient_Details in an unsorted manner:

SELECT * FROM Patient_Details;

The output of the above SELECT query is shown below:

Patient_ID	Patient_Name	Patient_Disease	Patient_Age	Patient_City
1035	Jones	Malaria	25	Goa
1015	Marry	Diabetes	44	Mumbai
1003	Harry	Fever	29	Kochi
1044	Ella	Cancer	40	Hyderabad
1025	Moria	Corona	52	Kolkata

The following query shows those records of patients in a sorted manner whose Patient_ID is greater than 1020:

SELECT * FROM Patient_Details WHERE Patient_ID >1020 ORDER BY Patient_IDASC;

Patient_ID	Patient_Name	Patient_Disease	Patient_Age	Patient_City
1025	Moria	Corona	52	Kolkata
1035	Jones	Malaria	25	Goa
1044	Ella	Cancer	40	Hyderabad

SQL Tutorial

SQL Database

SQL Table

SQL Select

SQL Clause

SQL Order By

SQL Insert

SQL Update

SQL Delete

SQL Join

SQL Keys

Difference

SQL Injection

SQL String Functions

Miscl

SQL MCQ

PL/SQL Tutorial

Sql Interview Question

SQl Quiz