SQL Order By Clause

Filed Under: SQL
SQL Order By

SQL Order By

  • SQL Order By clause is used for sorting the result set.
  • The sorting is done in either ascending or in descending order.
  • The default order of sorting is sorting in ascending order.
  • SQL Order By clause is used with SQL Select queries. Sorting is done after the result set is obtained
  • We can use multiple columns with order by clause, sorting will happen from left side columns first and then towards the left side columns. We will look at sql order by example with multiple columns to understand this scenario.

SQL Order By Syntax

Let’s now try to understand syntax for SQL ORDER BY clause. Below is the syntax for getting results in ascending order.


SELECT column FROM table_name ORDER BY column asc;

In the syntax above the column, data is retrieved in ascending order.

Let’s try to understand the ORDER BY command for ascending order through some example.

Let us consider the following Customer Table to understand ORDER BY command.

CustomerId CustomerName CustomerAge CustomerGender
1 John 31 M
2 Amit 25 M
3 Annie 35 F
4 null 38 M

Scenario: Retrieve name of the customers in ascending order.

Query:


SELECT CutomerName FROM Customer ORDER BY CustomerName asc;
CustomerName
null
Amit
Annie
John

SQL Order By Multiple Columns

We can sort the data based on multiple columns also. Let’s consider the following scenario to understand multiple column sorting.

Scenario: Retrieve customer data based on CustomerGender and CustomerName in ascending order.

Query:


SELECT * FROM Customer ORDER BY CustomerGender, CustomerName ASC;

Output:

CustomerId CustomerName CustomerAge CustomerGender
3 Annie 35 F
4 null 38 M
2 Amit 25 M
1 John 31 M

Notice that first sorting is done on gender and then on the name. By default “null” values comes first in ascending order values.

SQL Order By Descending


SELECT column FROM table_name ORDER BY column desc;

In the syntax above the column, data is retrieved in descending order.

Let’s try to understand the ORDER BY command for descending through some example. We will use the Customer table provided above for our example.

Scenario: Retrieve name of the customers in descending order.

Query:


SELECT CutomerName FROM Customer ORDER BY CustomerName desc;

Output:

CustomerName
John
Annie
Amit
null

SQL Order By Multiple Ascending Descending

Scenario: Retrieve customer data based on CustomerGender in ascending and CustomerName in descending order.

Query:


SELECT * FROM Customer ORDER BY CustomerGender ASC, CustomerName desc;

Output:

CustomerId CustomerName CustomerAge CustomerGender
3 Annie 35 F
1 John 31 M
2 Amit 25 M
4 null 38 M

That’s all for a quick roundup on SQL Order By clause.

Reference: Oracle Documentation

Leave a Reply

Your email address will not be published. Required fields are marked *

close
Generic selectors
Exact matches only
Search in title
Search in content
Search in posts
Search in pages