SQL - SORTING Results

The SQLORDER BYclause is used to sort the data in ascending or descending order, based on one or more columns. By default, some databases sort the query results in an ascending order.

In addition to that, ORDER BY clause can also sort the data in a database table in a preferred order. This case may not sort the records of a table in any standard order (like alphabetical or lexicographical), but, they could be sorted based on any external condition. For instance, in an ORDERS table containing the list of orders made by various customers of an organization, the details of orders placed can be sorted based on the dates on which those orders are made. This need not be alphabetically sorted, instead, it is based on “first come first serve”.

Syntax

The basic syntax of the ORDER BY clause which would be used to sort the result in an ascending or descending order is as follows −

SELECT column-list 
FROM table_name 
[WHERE condition] 
[ORDER BY column1, column2, .. columnN] [ASC | DESC];

You can use more than one column in the ORDER BY clause. Make sure that whatever column you are using to sort, that column should be in the column-list.

Sorting Results in Ascending Order

Using Order By Clause in SQL, the records in a database table can be sorted in ascending order, either by default or by specifying the “ASC” keyword in the clause condition. Let us see an example to understand this.

Example

Assume we have created a table namedCUSTOMERSusing the CREATE TABLE statement as shown below −

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),       
   PRIMARY KEY (ID)
);

Now, insert values into this table using the INSERT statement as follows −

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES 
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ),
(2, 'Khilan', 25, 'Delhi', 1500.00 ),
(3, 'kaushik', 23, 'Kota', 2000.00 ),
(4, 'Chaitali', 25, 'Mumbai', 6500.00 ),
(5, 'Hardik', 27, 'Bhopal', 8500.00 ),
(6, 'Komal', 22, 'Hyderabad', 4500.00 ),
(7, 'Muffy', 24, 'Indore', 10000.00 );

The table will be created as −

IDNAMEAGEADDRESSSALARY1Ramesh32Ahmedabad2000.002Khilan25Delhi1500.003kaushik23Kota2000.004Chaitali25Mumbai6500.005Hardik27Bhopal8500.006Komal22Hyderabad4500.007Muffy24Indore10000.00

Following is an example, which would sort the result in an ascending order by NAME and SALARY.

SELECT * FROM CUSTOMERS ORDER BY NAME;

Output

This would produce the following result −

IDNAMEAGEADDRESSSALARY4Chaitali25Mumbai6500.005Hardik27Bhopal8500.003kaushik23Kota2000.002Khilan25Delhi1500.006Komal22Hyderabad4500.007Muffy24Indore10000.001Ramesh32Ahmedabad2000.00

Sorting Results in Descending Order

But, to sort the records in a database table in descending order, we need to specify the “DESC” keyword in the clause condition. Let us see an example to understand this.

Example

The following query sorts the records of the CUSTOMERS tables in descending order based on the columnNAME.

SELECT * FROM CUSTOMERS ORDER BY NAME DESC;

Output

This would produce the following result −

IDNAMEAGEADDRESSSALARY1Ramesh32Ahmedabad2000.007Muffy24Indore10000.006Komal22Hyderabad4500.002Khilan25Delhi1500.003kaushik23Kota2000.005Hardik27Bhopal8500.004Chaitali25Mumbai6500.00

Sorting Results in a Preferred Order

One can also sort the records of a table in their own preferred order using the CASE statement within the ORDER BY clause. All the values are specified in the clause along with the position they are supposed to be sorted in; if the values are not given any number, they are automatically sorted in ascending order.

Example

To fetch the rows with their own preferred order, the SELECT query used would be as follows −

SELECT * FROM CUSTOMERS
ORDER BY ( CASE ADDRESS
   WHEN 'DELHI' 	 THEN 1
   WHEN 'BHOPAL' 	 THEN 2
   WHEN 'KOTA' 	 THEN 3
   WHEN 'AHMEDABAD' THEN 4
   WHEN 'Hyderabad' 	THEN 5
   ELSE 100 END) ASC, ADDRESS DESC;

Output

This would produce the following result −

IDNAMEAGEADDRESSSALARY2Khilan25Delhi1500.005Hardik27Bhopal8500.003kaushik23Kota2000.001Ramesh32Ahmedabad2000.006Komal22Hyderabad4500.004Chaitali25Mumbai6500.007Muffy24Indore10000.00

This will sort the customers by ADDRESS in yourown orderof preference first, and in a natural order for the remaining addresses. Also, the remaining Addresses will be sorted in the reverse alphabetical order.