This is a detailed guide on how you can use the SQL Order By Keyword to filter the records from a database table in either ascending or descending order.
Use of SQL Order By Keyword
Order By keyword is used to filter or sort the records in either ascending or descending order in an SQL query. By default, the ORDER BY keyword means to sort the result set into Ascending Order but if you want to sort the result set into Descending Order then you have to use another keyword DESC explicitly after the ORDER BY keyword.
This way you can sort the result set of a SQL statement according to one or more columns using the Order By Keyword.
Order By Syntax
SELECT col_1, col_2, ...
ORDER BY col_1, col_2, ... ASC|DESC;
As written in the above syntax, you can order the results according to a particular column or as well as according to the combination of more than once columns. If you don’t write ASC or DESC explicitly after the ORDER BY keyword and column(s) name(s), the result set will automatically be sorted in Ascending Order.
ORDER BY Examples
We’ve given below three examples with the screenshot of their result sets after using the ORDER BY Keyword in the SQL Select Statement. Here we’re using a demo database of
students table in MySQL. Given below is a snapshot of some table columns and data.
ORDER BY ASC Example
Two queries are written below. Both of these queries will give the same result-set. The rows in the result-set will be ordered in ascending order according to the column
SELECT * from students ORDER BY student_first_name
SELECT * from students ORDER BY student_first_name ASC
You can clearly see in the above screenshot the rows of the result set are now being strictly sorted in Ascending Order according to the column
ORDER BY DESC Example
I’ll use same SELECT statement as we used in the above example, it’s just here we’ll use the DESC keyword instead of ASC keyword with the ORDER BY clause.
SELECT * from students ORDER BY student_first_name DESC
The above screenshot of the result set clearly shows that the rows of the result set are sorted in descending order of the column student_first_name.
ORDER BY on Several Columns
In case you want to do the sorting of the result set according to more than one column, you can still make use of the ORDER BY clause. In our demo database and student table, we’ve two columns for the full student name, one is the
student_first_name and another is the
student_last_name. Most of the times we require the result set to sorted according to the combination of both of these columns.
Given below are the three examples of sorting the results according to these two columns in the ascending as well as descending order using the SQL Order By keyword.
ORDER BY Two Columns ASC Example
Both of the following queries will give the same result set as output.
SELECT * FROM students ORDER BY student_first_name, student_last_name
SELECT * FROM students ORDER BY student_first_name, student_last_name ASC
ORDER BY Two Columns DESC Example
Note. You’ve to define either ASC or DESC for individual columns.
SELECT * FROM students ORDER BY student_first_name DESC, student_last_name DESC
ORDER BY Two Columns, ONE BY ASC, ONE BY DESC
You might be wanted to sort the result according to multiple columns such that you want to sort according to the concept of one column being in Ascending Order and another one in the descending order. You can also sort the result set this way using the SQL ORDER BY clause. The following example query illustrates the same of the sorting of the results according to the ascending order of the first column and descending order of the second column.
SELECT * FROM students ORDER BY student_first_name ASC, student_last_name DESC
This way you can use the ORDER BY clause with as many columns as you want.
I hope you find this guide useful. If so, do share it with others who are willing to learn SQL. If you have any questions related to this article, feel free to ask them in the comments section.
Helpful Associated Guide.
Also, don’t forget to Subscribe to WTMatter!