ORDER By clause is used to to sort rows by a specified column
or columns in ascending i.e. lowest to
highest or descending i.e. highest to lowest order. This clause is always last
clause in a SELECT statement.
To sort by a column :
Syntax of sorting a single column by ORDER BY clause :
SELECT columns
FROM table
ORDER BY sort_column[ASC | DESC]
ASC – It is used to
sort in ascending order.
DESC – It is used to sort descending order.
Example of ORDER BY clause to sort column in ascending order using
select query :
SELECT ContactName,
City, Country
FROM Customers
ORDER
BY ContactName ASC
Output
ContactName City Country
Alejandra
Camino Madrid Spain
Alexander
Feuer Leipzig Germany
Bernardo
Batista Rio
de Janeiro Brazil
Carine
Schmitt Nantes France
Carlos
González Barquisimeto Venezuela
Carlos
Hernández San Cristóbal Venezuela
By default ORDER BY clause sorts in ascending order. So ASC
is optional.
Example of ORDER BY clause to sort column in descending order
using select query :
SELECT ContactName,
City, Country
FROM Customers
ORDER
BY ContactName DESC
Output
ContactName City Country
Carlos
Hernández San Cristóbal Venezuela
Carlos
González Barquisimeto Venezuela
Carine
Schmitt Nantes France
Bernardo
Batista Rio
de Janeiro Brazil
Alexander
Feuer Leipzig Germany
Alejandra
Camino Madrid Spain
DESC is used to sort records in descending order, so above
query returns records by sorted in descending ContactName.
To sort by multiple columns :
Syntax of sorting multiple columns by ORDER BY clause :
SELECT columns
FROM table
ORDER BY sort_column1[ASC | DESC],
sort_column2[ASC | DESC],
……
sort_columnN[ASC | DESC]
Example of sorting multiple columns by ORDER BY clause :
SELECT ContactName,
City, Country
FROM Customers
ORDER
BY
City,
Country
DESC
Output
ContactName City Country
Carlos González Barquisimeto Venezuela
Alexander Feuer Leipzig Germany
Alejandra Camino Madrid Spain
Carine Schmitt Nantes France
Bernardo Batista Rio de Janeiro Brazil
Carlos Hernández San Cristóbal Venezuela
Above query returns descending country within ascending city
i.e. City is sorted in ascending order and country is sorted by descending
within city.
To sort by relative column positions :
Syntax of ORDER BY clause to sort by relative column positions in select query :
SELECT columns
FROM table
ORDER BY sort_num1[ASC | DESC],
sort_num2[ASC | DESC],
……
sort_numN[ASC | DESC]
sort_num1, sort_num2… sort_numN are relative column
postion in select clause.
Example of sorting by specifing relative colmn position
numbers instead of column names in ORDER BY clause
SELECT ContactName,
City, Country
FROM Customers
ORDER
BY
2,
3 DESC
Output
ContactName City Country
Carlos González Barquisimeto Venezuela
Alexander Feuer Leipzig Germany
Alejandra Camino Madrid Spain
Carine Schmitt Nantes France
Bernardo Batista Rio de Janeiro Brazil
Carlos Hernández San Cristóbal Venezuela
In above query column city nad country are relatively on 2nd
and 3rd position. So city is sorted by ascending order and country
by descending order within city.
Note that you can sort by
columns that are not listed in the SELECT clause.
For example
SELECT ContactName,
City, Country
FROM Customers
ORDER
BY PostalCode
This technique won’t work for relative column positions.
You
can specify column aliases instead of column name in ORDER BY
clause.
For Example
SELECT ContactName AS CustomerName, City
FROM Customers
ORDER
BY CustomerName