Skip to content
Related Articles

Related Articles

Improve Article
Save Article
Like Article

SQL – ORDER BY

  • Difficulty Level : Easy
  • Last Updated : 04 Oct, 2021

The ORDER BY statement in SQL is used to sort the fetched data in either ascending or descending according to one or more columns. 

  • By default ORDER BY sorts the data in ascending order.
  • We can use the keyword DESC to sort the data in descending order and the keyword ASC to sort in ascending order.

Sort according to one column:

 To sort in ascending or descending order we can use the keywords ASC or DESC respectively. 

Attention reader! Don’t stop learning now. Learn SQL for interviews using SQL Course  by GeeksforGeeks.

Syntax:

SELECT * FROM table_name ORDER BY column_name ASC|DESC
//Where 
table_name: name of the table.
column_name: name of the column according to which the data is needed to be arranged.
ASC: to sort the data in ascending order.
DESC: to sort the data in descending order.
| : use either ASC or DESC to sort in ascending or descending order//

Sort according to multiple columns: 

To sort in ascending or descending order we can use the keywords ASC or DESC respectively. To sort according to multiple columns, separate the names of columns by the (,) operator. 



Syntax: 

SELECT * FROM table_name ORDER BY column1 ASC|DESC , column2 ASC|DESC

Screenshot from 2016-12-19 12-53-29

Now consider the above database table and find the results of different queries.

Sort according to a single column

In this example, we will fetch all data from the table Student and sort the result in descending order according to the column ROLL_NO. 

Query:

SELECT * FROM Student ORDER BY ROLL_NO DESC;

Output: 

ROLL_NONAMEADDRESSPHONEAge
8NIRAJALIPURXXXXXXXXXX19
7ROHITBALURGHATXXXXXXXXXX18
6DHANRAJBARABAJARXXXXXXXXXX20
5SAPTARHIKOLKATAXXXXXXXXXX19
4DEEPRAMNAGARXXXXXXXXXX18
3RIYANKASILIGURIXXXXXXXXXX20
2PRATIKBIHARXXXXXXXXXX19
1HARSHDELHIXXXXXXXXXX18

In the above example, if we want to sort in ascending order we have to use ASC in place of DESC.

Sort according to multiple columns:

In this example we will fetch all data from the table Student and then sort the result in ascending order first according to the column Age. and then in descending order according to the column ROLL_NO. 
Query:



SELECT * FROM Student ORDER BY Age ASC , ROLL_NO DESC;

Output: 

ROLL_NONAMEADDRESSPHONEAge
7ROHITBALURGHATXXXXXXXXXX18
4DEEPRAMNAGARXXXXXXXXXX18
1HARSHDELHIXXXXXXXXXX18
8NIRAJALIPURXXXXXXXXXX19
5SAPTARHIKOLKATAXXXXXXXXXX19
2PRATIKBIHARXXXXXXXXXX19
6DHANRAJBARABAJARXXXXXXXXXX20
3RIYANKASILIGURIXXXXXXXXXX20

In the above output, we can see that first the result is sorted in ascending order according to Age. There are multiple rows of having the same Age. Now, sorting further this result-set according to ROLL_NO will sort the rows with the same Age according to ROLL_NO in descending order.

Note:

ASC is the default value for the ORDER BY clause. So, if we don’t specify anything after the column name in the ORDER BY clause, the output will be sorted in ascending order by default. 

Take another example of the following query will give similar output as the above: 
Query:

SELECT * FROM Student ORDER BY Age , ROLL_NO DESC;

Output: 

ROLL_NONAMEADDRESSPHONEAge
7ROHITBALURGHATXXXXXXXXXX18
4DEEPRAMNAGARXXXXXXXXXX18
1HARSHDELHIXXXXXXXXXX18
8NIRAJALIPURXXXXXXXXXX19
5SAPTARHIKOLKATAXXXXXXXXXX19
2PRATIKBIHARXXXXXXXXXX19
6DHANRAJBARABAJARXXXXXXXXXX20
3RIYANKASILIGURIXXXXXXXXXX20

Sorting by column number (instead of name):

An integer that identifies the number of the column in the SelectItems in the underlying query of the SELECT statement. Column number must be greater than 0 and not greater than the number of columns in the result table. In other words, if we want to order by a column, that column must be specified in the SELECT list.

The rule checks for ORDER BY clauses that reference select list columns using the column number instead of the column name. The column numbers in the ORDER BY clause impairs the readability of the SQL statement. Further, changing the order of columns in the SELECT list has no impact on the ORDER BY when the columns are referred by names instead of numbers. 

Syntax:

Order by Column_Number asc/desc

Here we take an example to sort a database table according to column 1 i.e Roll_Number. For this a query will be:

Query:

CREATE TABLE studentinfo
( Roll_no INT,
NAME VARCHAR(25),
Address VARCHAR(20),
CONTACTNO BIGINT NOT NULL,
Age INT ); 
INSERT INTO studentinfo
VALUES (7,'ROHIT','GAZIABAD',9193458625,18),
(4,'DEEP','RAMNAGAR',9193458546,18),
(1,'HARSH','DELHI',9193342625,18),
(8,'NIRAJ','ALIPUR',9193678625,19),
(5,'SAPTARHI','KOLKATA',9193789625,19),
(2,'PRATIK','BIHAR',9193457825,19),
(6,'DHANRAJ','BARABAJAR',9193358625,20),
(3,'RIYANKA','SILIGURI',9193218625,20);
SELECT Name, Address
FROM studentinfo
ORDER BY 1

Output:

 




My Personal Notes arrow_drop_up
Recommended Articles
Page :