"ORDER BY" is used in SQL to display the data in desired order based on the column or colums provided in the order by. It could be Ascending (ASC) order or descending (DESC) .
Syntax:
order by desired_column
order by desired_column ASC
order by desired_column DESC
order by column1 , column2, column3, …..You can have one, two , three or all the columns in the order by keyword.
"ORDER BY" is used at the end of the script.
Let's take a table EMP_INFO as below:
NAME
|
DEPARTMENT
|
SALARY
|
LOCATION
|
POSITION
|
STATE
|
JOINDATE
|
DAVID
|
MARKETING
|
2000
|
CHICAGO
|
MANAGER
|
IL
|
12-Dec-00
|
RICHARD
|
SALES
|
1000
|
BALTIMORE
|
SALESMAN
|
MD
|
1-Jan-08
|
SMITH
|
RESOURCES
|
1500
|
DALLAS
|
HR
|
TX
|
5-Jan-06
|
RAM
|
RESOURCES
|
1200
|
DALLAS
|
ASSISTANT
|
TX
|
5-Jun-06
|
GOPI
|
SALES
|
1100
|
AUSTIN
|
MARKETER
|
TX
|
9-Feb-08
|
BRITNI
|
TECHNICAL
|
1800
|
BALTIMORE
|
ASSISTANT
|
MD
|
1-Feb-09
|
ADAM
|
TECHNICAL
|
2200
|
BALTIMORE
|
LEAD
|
MD
|
1-Mar-10
|
MIKE
|
EXECUTIVE
|
2500
|
CHICAGO
|
DIRECTOR
|
IL
|
1-Aug-10
|
JAMES
|
MARKETING
|
1200
|
RESTON
|
ACCOUNTANT
|
VA
|
20-Aug-14
|
JULI
|
EXECUTIVE
|
2200
|
ARLINGTON
|
MANAGER
|
VA
|
21-Aug-12
|
order by is ASC gives the data ordered by numerical value in ascending order or date in ascending order or alphabets in alphabetical order.
By default, order by is ASC. let's see below two results without ASC and with ASC, both are giving same result.
SELECT * FROM EMP_INFO ORDER BY SALARY; --Salary has been ordered in ascending order and all other columns are ordered based on the salary column.
NAME
|
DEPARTMENT
|
SALARY
|
LOCATION
|
POSITION
|
STATE
|
JOINDATE
|
RICHARD
|
SALES
|
1000
|
BALTIMORE
|
SALESMAN
|
MD
|
1-Jan-08
|
GOPI
|
SALES
|
1100
|
AUSTIN
|
MARKETER
|
TX
|
9-Feb-08
|
RAM
|
RESOURCES
|
1200
|
DALLAS
|
ASSISTANT
|
TX
|
5-Jun-06
|
JAMES
|
MARKETING
|
1200
|
RESTON
|
ACCOUNTANT
|
VA
|
20-Aug-14
|
SMITH
|
RESOURCES
|
1500
|
DALLAS
|
HR
|
TX
|
5-Jan-06
|
BRITNI
|
TECHNICAL
|
1800
|
BALTIMORE
|
ASSISTANT
|
MD
|
1-Feb-09
|
DAVID
|
MARKETING
|
2000
|
CHICAGO
|
MANAGER
|
IL
|
12-Dec-00
|
JULI
|
EXECUTIVE
|
2200
|
ARLINGTON
|
MANAGER
|
VA
|
21-Aug-12
|
ADAM
|
TECHNICAL
|
2200
|
BALTIMORE
|
LEAD
|
MD
|
1-Mar-10
|
MIKE
|
EXECUTIVE
|
2500
|
CHICAGO
|
DIRECTOR
|
IL
|
1-Aug-10
|
SELECT * FROM EMP_INFO ORDER BY SALARY ASC; -- This query is also showing same result like above , this proves that ASC is by default .
NAME
|
DEPARTMENT
|
SALARY
|
LOCATION
|
POSITION
|
STATE
|
JOINDATE
|
RICHARD
|
SALES
|
1000
|
BALTIMORE
|
SALESMAN
|
MD
|
1-Jan-08
|
GOPI
|
SALES
|
1100
|
AUSTIN
|
MARKETER
|
TX
|
9-Feb-08
|
RAM
|
RESOURCES
|
1200
|
DALLAS
|
ASSISTANT
|
TX
|
5-Jun-06
|
JAMES
|
MARKETING
|
1200
|
RESTON
|
ACCOUNTANT
|
VA
|
20-Aug-14
|
SMITH
|
RESOURCES
|
1500
|
DALLAS
|
HR
|
TX
|
5-Jan-06
|
BRITNI
|
TECHNICAL
|
1800
|
BALTIMORE
|
ASSISTANT
|
MD
|
1-Feb-09
|
DAVID
|
MARKETING
|
2000
|
CHICAGO
|
MANAGER
|
IL
|
12-Dec-00
|
JULI
|
EXECUTIVE
|
2200
|
ARLINGTON
|
MANAGER
|
VA
|
21-Aug-12
|
ADAM
|
TECHNICAL
|
2200
|
BALTIMORE
|
LEAD
|
MD
|
1-Mar-10
|
MIKE
|
EXECUTIVE
|
2500
|
CHICAGO
|
DIRECTOR
|
IL
|
1-Aug-10
|
order by desired_column DESC
This is opposite of "order by ASC" , ie, it orders in the descending order.
SELECT * FROM EMP_INFO ORDER BY SALARY DESC; -- in this all the data have been ordered based on the salary value in the descending order.
NAME
|
DEPARTMENT
|
SALARY
|
LOCATION
|
POSITION
|
STATE
|
JOINDATE
|
MIKE
|
EXECUTIVE
|
2500
|
CHICAGO
|
DIRECTOR
|
IL
|
1-Aug-10
|
JULI
|
EXECUTIVE
|
2200
|
ARLINGTON
|
MANAGER
|
VA
|
21-Aug-12
|
ADAM
|
TECHNICAL
|
2200
|
BALTIMORE
|
LEAD
|
MD
|
1-Mar-10
|
DAVID
|
MARKETING
|
2000
|
CHICAGO
|
MANAGER
|
IL
|
12-Dec-00
|
BRITNI
|
TECHNICAL
|
1800
|
BALTIMORE
|
ASSISTANT
|
MD
|
1-Feb-09
|
SMITH
|
RESOURCES
|
1500
|
DALLAS
|
HR
|
TX
|
5-Jan-06
|
JAMES
|
MARKETING
|
1200
|
RESTON
|
ACCOUNTANT
|
VA
|
20-Aug-14
|
RAM
|
RESOURCES
|
1200
|
DALLAS
|
ASSISTANT
|
TX
|
5-Jun-06
|
GOPI
|
SALES
|
1100
|
AUSTIN
|
MARKETER
|
TX
|
9-Feb-08
|
RICHARD
|
SALES
|
1000
|
BALTIMORE
|
SALESMAN
|
MD
|
1-Jan-08
|
You can order by more than one columns.
To understand how it works when more than one columns are there, let's take below example.
Let's order by two columns salary and location. In this situation, it first orders by salary. If there are similar values in salary in more than one rows then it further orders the data based on second column in order by and so on.
In below example, three rows are having the salary = 1200. So, for these three rows, it further orders by the location because the query contains order by salary, location. Same rule applies to two rows having salary =2200 as shown below.
SELECT * FROM EMP_INFO
ORDER by SALARY, LOCATION;
NAME
|
DEPARTMENT
|
SALARY
|
LOCATION
|
POSITION
|
STATE
|
JOINDATE
|
RICHARD
|
SALES
|
1000
|
BALTIMORE
|
SALESMAN
|
MD
|
1-Jan-08
|
GOPI
|
SALES
|
1200
|
AUSTIN
|
MARKETER
|
TX
|
9-Feb-08
|
RAM
|
RESOURCES
|
1200
|
DALLAS
|
ASSISTANT
|
TX
|
5-Jun-06
|
JAMES
|
MARKETING
|
1200
|
RESTON
|
ACCOUNTANT
|
VA
|
20-Aug-14
|
SMITH
|
RESOURCES
|
1500
|
DALLAS
|
HR
|
TX
|
5-Jan-06
|
BRITNI
|
TECHNICAL
|
1800
|
BALTIMORE
|
ASSISTANT
|
MD
|
1-Feb-09
|
DAVID
|
MARKETING
|
2000
|
CHICAGO
|
MANAGER
|
IL
|
12-Dec-00
|
JULI
|
EXECUTIVE
|
2200
|
ARLINGTON
|
MANAGER
|
VA
|
21-Aug-12
|
ADAM
|
TECHNICAL
|
2200
|
BALTIMORE
|
LEAD
|
MD
|
1-Mar-10
|
MIKE
|
EXECUTIVE
|
2500
|
CHICAGO
|
DIRECTOR
|
IL
|
1-Aug-10
|
As shown above, if there were no second column in order by , then the three rows with Salary=1200 would be displayed in any order if there were no second column(location) in order by.
When you are ordering by more than one columns, you can order by combination of ASC and DESC as per your need.
SELECT * FROM EMP_INFO
SELECT * FROM EMP_INFO
ORDER by SALARY ASC , LOCATION DESC;
After running above query, As you can see in below result, the salary is ordered in ascending order where as the location is ordered in descending order.
After running above query, As you can see in below result, the salary is ordered in ascending order where as the location is ordered in descending order.
NAME
|
DEPARTMENT
|
SALARY
|
LOCATION
|
POSITION
|
STATE
|
JOINDATE
|
RICHARD
|
SALES
|
1000
|
BALTIMORE
|
SALESMAN
|
MD
|
1-Jan-08
|
JAMES
|
MARKETING
|
1200
|
RESTON
|
ACCOUNTANT
|
VA
|
20-Aug-14
|
RAM
|
RESOURCES
|
1200
|
DALLAS
|
ASSISTANT
|
TX
|
5-Jun-06
|
GOPI
|
SALES
|
1200
|
AUSTIN
|
MARKETER
|
TX
|
9-Feb-08
|
SMITH
|
RESOURCES
|
1500
|
DALLAS
|
HR
|
TX
|
5-Jan-06
|
BRITNI
|
TECHNICAL
|
1800
|
BALTIMORE
|
ASSISTANT
|
MD
|
1-Feb-09
|
DAVID
|
MARKETING
|
2000
|
CHICAGO
|
MANAGER
|
IL
|
12-Dec-00
|
ADAM
|
TECHNICAL
|
2200
|
BALTIMORE
|
LEAD
|
MD
|
1-Mar-10
|
JULI
|
EXECUTIVE
|
2200
|
ARLINGTON
|
MANAGER
|
VA
|
21-Aug-12
|
MIKE
|
EXECUTIVE
|
2500
|
CHICAGO
|
DIRECTOR
|
IL
|
1-Aug-10
|
"ORDER BY" can be based on number , alphabets , date etc. We saw , order by numerical value in above examples.
Let's see order by Alphabet :
SELECT * FROM EMP_INFO
ORDER by POSITION;
ORDER by POSITION;
NAME
|
DEPARTMENT
|
SALARY
|
LOCATION
|
POSITION
|
STATE
|
JOINDATE
|
JAMES
|
MARKETING
|
1200
|
RESTON
|
ACCOUNTANT
|
VA
|
20-Aug-14
|
RAM
|
RESOURCES
|
1200
|
DALLAS
|
ASSISTANT
|
TX
|
5-Jun-06
|
BRITNI
|
TECHNICAL
|
1800
|
BALTIMORE
|
ASSISTANT
|
MD
|
1-Feb-09
|
MIKE
|
EXECUTIVE
|
2500
|
CHICAGO
|
DIRECTOR
|
IL
|
1-Aug-10
|
SMITH
|
RESOURCES
|
1500
|
DALLAS
|
HR
|
TX
|
5-Jan-06
|
ADAM
|
TECHNICAL
|
2200
|
BALTIMORE
|
LEAD
|
MD
|
1-Mar-10
|
DAVID
|
MARKETING
|
2000
|
CHICAGO
|
MANAGER
|
IL
|
12-Dec-00
|
JULI
|
EXECUTIVE
|
2200
|
ARLINGTON
|
MANAGER
|
VA
|
21-Aug-12
|
GOPI
|
SALES
|
1200
|
AUSTIN
|
MARKETER
|
TX
|
9-Feb-08
|
RICHARD
|
SALES
|
1000
|
BALTIMORE
|
SALESMAN
|
MD
|
1-Jan-08
|
Let's see order by DATE :
SELECT * FROM EMP_INFO
ORDER by JOINDATE;
ORDER by JOINDATE;
NAME
|
DEPARTMENT
|
SALARY
|
LOCATION
|
POSITION
|
STATE
|
JOINDATE
|
DAVID
|
MARKETING
|
2000
|
CHICAGO
|
MANAGER
|
IL
|
12-Dec-00
|
SMITH
|
RESOURCES
|
1500
|
DALLAS
|
HR
|
TX
|
5-Jan-06
|
RAM
|
RESOURCES
|
1200
|
DALLAS
|
ASSISTANT
|
TX
|
5-Jun-06
|
RICHARD
|
SALES
|
1000
|
BALTIMORE
|
SALESMAN
|
MD
|
1-Jan-08
|
GOPI
|
SALES
|
1200
|
AUSTIN
|
MARKETER
|
TX
|
9-Feb-08
|
BRITNI
|
TECHNICAL
|
1800
|
BALTIMORE
|
ASSISTANT
|
MD
|
1-Feb-09
|
ADAM
|
TECHNICAL
|
2200
|
BALTIMORE
|
LEAD
|
MD
|
1-Mar-10
|
MIKE
|
EXECUTIVE
|
2500
|
CHICAGO
|
DIRECTOR
|
IL
|
1-Aug-10
|
JULI
|
EXECUTIVE
|
2200
|
ARLINGTON
|
MANAGER
|
VA
|
21-Aug-12
|
JAMES
|
MARKETING
|
1200
|
RESTON
|
ACCOUNTANT
|
VA
|
20-Aug-14
|
Instead of column name, you can order by respective number of the column used in the select statement. .
SELECT * FROM EMP_INFO;
In this statement the position of the column NAME is 1, DEPARTMENT is 2 and so on.
Let's order by 6 , ie, STATE.
SELECT * FROM EMP_INFO
ORDER by 6;
NAME
|
DEPARTMENT
|
SALARY
|
LOCATION
|
POSITION
|
STATE
|
JOINDATE
|
DAVID
|
MARKETING
|
2000
|
CHICAGO
|
MANAGER
|
IL
|
12-Dec-00
|
MIKE
|
EXECUTIVE
|
2500
|
CHICAGO
|
DIRECTOR
|
IL
|
1-Aug-10
|
ADAM
|
TECHNICAL
|
2200
|
BALTIMORE
|
LEAD
|
MD
|
1-Mar-10
|
BRITNI
|
TECHNICAL
|
1800
|
BALTIMORE
|
ASSISTANT
|
MD
|
1-Feb-09
|
RICHARD
|
SALES
|
1000
|
BALTIMORE
|
SALESMAN
|
MD
|
1-Jan-08
|
RAM
|
RESOURCES
|
1200
|
DALLAS
|
ASSISTANT
|
TX
|
5-Jun-06
|
SMITH
|
RESOURCES
|
1500
|
DALLAS
|
HR
|
TX
|
5-Jan-06
|
GOPI
|
SALES
|
1200
|
AUSTIN
|
MARKETER
|
TX
|
9-Feb-08
|
JAMES
|
MARKETING
|
1200
|
RESTON
|
ACCOUNTANT
|
VA
|
20-Aug-14
|
JULI
|
EXECUTIVE
|
2200
|
ARLINGTON
|
MANAGER
|
VA
|
21-Aug-12
|
Let's order by LOCATION in below select statement using numerical representation of the column.
Select DEPARTMENT , LOCATION , STATE FROM EMP_INFO
ORDER BY 2 ;
|
DEPARTMENT
|
LOCATION
|
STATE
|
|
EXECUTIVE
|
ARLINGTON
|
VA
|
|
SALES
|
AUSTIN
|
TX
|
|
TECHNICAL
|
BALTIMORE
|
MD
|
|
TECHNICAL
|
BALTIMORE
|
MD
|
|
SALES
|
BALTIMORE
|
MD
|
|
MARKETING
|
CHICAGO
|
IL
|
|
EXECUTIVE
|
CHICAGO
|
IL
|
|
RESOURCES
|
DALLAS
|
TX
|
|
RESOURCES
|
DALLAS
|
TX
|
|
MARKETING
|
RESTON
|
VA
|
NEXT
No comments:
Post a Comment