Logo 
Search:

SQL Server Articles

Submit Article
Home » Articles » SQL Server » MiscellaneousRSS Feeds

Sorting Rows with ORDER BY

Posted By: Sarita Patel     Category: SQL Server     Views: 3938

This article describes use of ORDER BY clause to sort single columns, multiple columns, relative column position in sql server.

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 

  
Share: 


Didn't find what you were looking for? Find more on Sorting Rows with ORDER BY Or get search suggestion and latest updates.

Sarita Patel
Sarita Patel author of Sorting Rows with ORDER BY is from United States.
 
View All Articles

Related Articles and Code:


 
Please enter your Comment

  • Comment should be atleast 30 Characters.
  • Please put code inside [Code] your code [/Code].

 
No Comment Found, Be the First to post comment!