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

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:

1. Create a Simple Database and Table.
2. Insert Data into the Table.
3. View the Inserted Data without ORDER BY clause.
4. 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:

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

Step 2: Insert the Data into the table

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

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

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

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

The output of the above SELECT query is shown below:

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:

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

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

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:

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

Example of ORDER BY ASC

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

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

The following query shows the record of the above table:

Output:

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

Output:

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

Example of ORDER BY DESC

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

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

The following query shows the record of the above table:

Output:

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

Output:

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

Example of ORDER BY with WHERE Clause

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

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

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

The output of the above SELECT query is shown below:

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