How to ORDER BY Multiple Columns in SQL?
FREESQL (Structured Query Language) is used to interact with the database. Standard SQL commands include 'select', 'delete', 'alter', etc. In SQL, we use the ORDER BY keyword to specify the order in which rows appear in the result set. We can sort the rows in the appropriate order using the ORDER BY clause. The ORDER BY clause is used to order the returned records. We can arrange the results in ascending or descending order by using the ORDER BY clause. This clause can also be used with multiple columns.
SQL Server ORDER BY Clause
The order of rows in the result set is not guaranteed when using the SELECT statement to query data from a table. It indicates that SQL Server can return a result set with rows in any order.
The ORDER BY clause is the only way to ensure that the rows in the result set are sorted. The ORDER BY clause is used to sort the result set in ascending or descending order. By default, the ORDER BY keyword sorts the records in ascending order.
Syntax
| Parameter | Description |
|---|---|
| column_name | expression | It is a column name or expression for sorting the result set of the query. If you give multiple columns, the result set is sorted first by the first column, then by the second column, and so on. Columns in the ORDER BY clause must correspond to either a select list column or columns defined in the table specified in the FROM clause. |
| ASC | DESC | The letters ASC or DESC indicate whether the values in the specified column should be ordered ascending or descending. The ASC sort order is from lowest to the highest value, while the DESC sort order is from highest to lowest. SQL Server uses ASC as the default sort order unless you explicitly specify DESC. In addition, SQL Server considers NULL to be the lowest value. |
Steps to ORDER BY Multiple Columns in SQL
For applying for the ORDER BY clause to multiple column order, add the name of the column by which you want to order records first. The column that is entered in the first place will get sorted likewise.
Step - 1 : Create a Simple Database and Table
First, you must create a new SQL database.
The Hospital Database is created using the following query :
Step - 2 : Insert the Data into the Table
Now, you have to insert the data into the table. So, that ORDER BY clause can be applied to the inserted data.
The following query inserts a record of admitted patients into the Patient table of the Hospital database :
Step - 3 : View the Inserted Data of the Table without Using the ORDER BY Clause
The following query returns an unsorted list of Patients :
The following is the output of the following SELECT query :
| Patient_ID | Patient_Name | Patient_Disease | Patient_Age | Patient_City |
|---|---|---|---|---|
| 1035 | Jai | Malaria | 25 | Goa |
| 1015 | Mohit | Diabetes | 44 | Mumbai |
| 1003 | Hariyash | Fever | 29 | Kochi |
| 1044 | Ekta | Cancer | 40 | Hyderabad |
| 1025 | Mohan | Corona | 52 | Kolkata |
Step - 4 : Use the ORDER BY Clause
The following query uses the ORDER BY clause to display all patient records by Patient_ID in ascending order :
The following is the output of the following SELECT query with ORDER BY clause :
| Patient_ID | Patient_Name | Patient_Disease | Patient_Age | Patient_City |
|---|---|---|---|---|
| 1003 | Hariyash | Fever | 29 | Kochi |
| 1015 | Mohit | Diabetes | 44 | Mumbai |
| 1025 | Mohan | Corona | 52 | Kolkata |
| 1035 | Jai | Malaria | 25 | Goa |
| 1044 | Ekta | Cancer | 40 | Hyderabad |
This query first sorts patient records by Patient_Name, then if Patient_Name appears more than once, it sorts those rows by Patient_Age.
| Patient_ID | Patient_Name | Patient_Age |
|---|---|---|
| 1044 | Ekta | 40 |
| 1003 | Hariyash | 29 |
| 1035 | Jai | 25 |
| 1025 | Mohan | 52 |
| 1015 | Mohit | 44 |
Examples
We will use the customer's table in the sample database from the demonstration.
| customers_sales |
|---|
| customer_id |
| First_Name |
| Last_Name |
| phone |
| street |
| city |
| state |
| zip_code |
Example - 1 :
In this example, we have not specified ASC or DESC in the ORDER BY clause and used ASC as default.
The customer list is sorted by first name in ascending order using the following query :
| First_Name | Last_Name |
|---|---|
| Aaron | Knapp |
| Abbey | Pugh |
| Abby | Gamble |
| Abram | Copeland |
| Adam | Henderson |
| Adam | Thomton |
| Addie | Hahn |
Example - 2 :
In this example, the ORDER BY clause sorted the result set according to values in the First_Name column in decreasing order because the DESC was explicitly specified.
The customer list is sorted by first name in descending order using the following query :
| First_Name | Last_Name |
|---|---|
| Zulema | Browning |
| Zulema | Clemons |
| Zoraida | Patton |
| Zora | Ford |
| Zona | Cameron |
| Zina | Bonner |
| Zenia | Bruce |
| Zelma | Browning |
Example - 3 :
In this example, the ORDER BY clause sorted the customer list by the city first, then by the first name.
The following statement retrieves the customers first_name, last_name, and city. It initially sorts the customer list by city first, then by first name.
| city | First_Name | Last_Name |
|---|---|---|
| Albany | Douglass | Blankenship |
| Albany | Mi | Gray |
| Albany | Priscilla | Wilkins |
| Amarillo | Andria | Rivers |
| Amarillo | Delaine | Estes |
| Amarillo | Jonell | Rivas |
| Amarillo | Luis | Tyler |
| Amarillo | Narcisa | Knapp |
Example - 4 :
In this example, the ORDER BY clause sorts the city in descending order and then sorts the sorted result in ascending order by the first name.
The following statement sorts the customers in descending order by city and then sorts the sorted result set in ascending order by the first name.
| city | First_Name | Last_Name |
|---|---|---|
| Yuba City | Louanne | Martin |
| Yorktown Heights | Demarcus | Reese |
| Yorktown Heights | Jenna | Saunders |
| Yorktown Heights | Latricia | Lindsey |
| Yorktown Heights | Shasta | Combs |
| Yorktown Heights | Shauna | Edwards |
| Yonkers | Aaron | Knapp |
| Yonkers | Alane | Munoz |
Learn More
Conclusion
- With the ORDER BY clause, you can select records from a database while also requesting that they be sorted by two columns. This clause is always added at the end of the SQL query.
- Add the name of the column by which you want to arrange records after the ORDER BY keyword. The second column is then added followed by a comma. You can change the sorting order (ascending or descending) for each column separately.
- You can use the ASC keyword to use ascending (low to high) order, however, this keyword is optional because it is the default order when nothing is specified. Put the DESC keyword after the appropriate column if you want to sort the data in descending order.