SQL Tutorial

SQL Tutorial SQL Introduction SQL Syntax SQL OPERATORS SQL COMMANDS SQL Queries

SQL Table

SQL TABLE SQL CREATE TABLE SQL ALTER TABLE SQL DELETE SQL TRUNCATE TABLE SQL UPDATE TABLE

SQL SELECT

SQL SELECT Statement SQL SELECT IN Operator SQL BETWEEN Operator SQL SELECT BETWEEN Operator SQL SELECT AND Operator SQL SELECT DISTINCT SQL SELECT SUM SQL SELECT MAX SQL SELECT MIN SQL SELECT AVG

SQL Clause

SQL GROUP BY CLAUSE SQL ORDER BY Clause SQL HAVING Clause

SQL INSERT

SQL INSERT INTO Statement

SQL JOIN

SQL JOIN SQL Inner Join SQL Left Join SQL Right Join

SQL OPERATOR

SQL Comparison SQL LOGICAL Operator SQL Arithmetic

Difference

SQL vs NOSQL WHERE vs HAVING DELETE vs DROP GROUP BY vs ORDER BY DROP vs TRUNCATE SQL IN vs SQL EXISTS

MISC

SQL SubQuery SQL CASE Commit and Rollback in SQL Pattern Matching in SQL DDL Commands in SQL DML Commands in SQL Types of SQL Commands SQL COUNT SQL Primary Key SQL FOREIGN KEY SET Operators in SQL Check Constraint in SQL SQL EXCEPT SQL VIEW SQL WHERE Statement SQL CRUD Operation Where Condition in SQL TCL Commands in SQL Types of SQL JOINS SQL Nth Highest Salary SQL NOT OPERATOR SQL UNION ALL SQL INTERSECT SQL Data Definition Language SQL Data Manipulation Language SQL Data Control Language SQL CONSTRAINTS SQL KEYS Codd’s Rules in SQL What is SQL Injection? Trigger In SQL SQL WHERE Multiple Conditions Truncate function in SQL

How To

How to use the BETWEEN operator in SQL How To Use INNER JOIN In SQL How to use LIKE in SQL How to use HAVING Clause in SQL How to use GROUP BY Clause in SQL How To Remove Duplicates In SQL How To Delete A Row In SQL How to add column in table in SQL ? How to drop a column in SQL? How to create a database in SQL? How to use COUNT in SQL? How to Create Temporary Table in SQL? How to Add Foreign Key in SQL? How to Add Comments in SQL? How To Use Group By Clause In SQL How To Use Having Clause In SQL How To Delete Column In Table How To Compare Date In SQL

SQL ORDER BY

SQL ORDER BY

The SQL ORDER BY clause is used to sort the data stored in tables in the database. The sorting can be done in an ascending way, descending way, or based on multiple columns of the table.

Generally, the default sorting order is ascending order.

SELECT expressions
 FROM tables
 WHERE conditions
 ORDER BY expression ASC | DESC; 

The following is the syntax of a general ORDER BY clause:

Example:

Let’s consider the following Customers table.

IDNAMEAGEADDRESSSALARY
1Himani gupta21Modinagar22000
2Shiva tiwari22Bhopal21000
3Ajeet bhargav45Meerut65000
4Ritesh yadav36Azamgarh26000
5Balwant singh45Varanasi36000
6Mahesh sharma26Mathura22000
 SELECT * 
 FROM Customers
 ORDER BY NAME, SALARY; 

The above query will sort all the records in ascending order based on the NAME and SALARY columns.

Output:

IDNAMEAGEADDRESSSALARY
3Ajeet bhargav45Meerut65000
5Balwant singh45Varanasi36000
1Himani gupta21Modinagar22000
6Mahesh sharma26Mathura22000
4Ritesh yadav36Azamgarh26000
2Shiva tiwari22Bhopal21000
SELECT *
 FROM CUSTOMERS
 ORDER BY NAME DESC; 

The above query will sort all the records in the table in a descending manner based on the NAME column.

Output:

IDNAMEAGEADDRESSSALARY
2Shiva tiwari22Bhopal21000
4Ritesh yadav36Azamgarh26000
6Mahesh sharma26Mathura22000
1Himani gupta21Modinagar22000
5Balwant singh45Varanasi36000
3Ajeet bhargav45Meerut65000

There are various topics under SQL ORDER BY clause. Namely, they are:

  • SQL ORDER BY ASC – Sorts the data in ascending manner
  • SQL ORDER BY DESC – Sorts the data in descending manner
  • SQL ORDER BY RANDOM – Sorts random data
  • SQL ORDER BY LIMIT – Selects limited data from the database
  • SQL ORDER BY Multiple Columns – Sorts the data based on multiple columns.

Let’s discuss each of them in detail.

SQL ORDER BY ASC

SQL ORDER BY ASC clause sorts the data in ascending order based on some columns. Generally, the default sorting order is ascending manner.

Example:

Let’s consider the following Customers table.

IDNAMEAGEADDRESSSALARY
1Himani gupta21Modinagar22000
2Shiva tiwari22Bhopal21000
3Ajeet bhargav45Meerut65000
4Ritesh yadav36Azamgarh26000
5Balwant singh45Varanasi36000
6Mahesh sharma26Mathura22000
SELECT * 
 FROM Customers
 ORDER BY NAME, SALARY; 

The above query will sort all the records in ascending order based on the NAME and SALARY columns.

Output:

IDNAMEAGEADDRESSSALARY
3Ajeet bhargav45Meerut65000
5Balwant singh45Varanasi36000
1Himani gupta21Modinagar22000
6Mahesh sharma26Mathura22000
4Ritesh yadav36Azamgarh26000
2Shiva tiwari22Bhopal21000

SQL ORDER BY DESC

SQL ORDER BY DESC clause sorts the data in descending order based on some columns.

Example:

Let’s consider the following Customers table.

IDNAMEAGEADDRESSSALARY
1Himani gupta21Modinagar22000
2Shiva tiwari22Bhopal21000
3Ajeet bhargav45Meerut65000
4Ritesh yadav36Azamgarh26000
5Balwant singh45Varanasi36000
6Mahesh sharma26Mathura22000
SELECT *
 FROM CUSTOMERS
 ORDER BY NAME DESC; 

The above query will sort all the records in the table in a descending manner based on the NAME column.

Output:

IDNAMEAGEADDRESSSALARY
2Shiva tiwari22Bhopal21000
4Ritesh yadav36Azamgarh26000
6Mahesh sharma26Mathura22000
1Himani gupta21Modinagar22000
5Balwant singh45Varanasi36000
3Ajeet bhargav45Meerut65000

SQL ORDER BY LIMIT

The SQL ORDER BY LIMIT clause helps in retrieving specific number of records from the database table. It is generally used in situations where there are large number of tuples which satisfy the given condition. It sets an upper limit to the number of records that are being retrieved.

In order to use the LIMIT clause in SQL, one has to use the ROWNUM clause with it.

Note: It should be noted that the LIMIT clause is not supported by all SQL versions.

This clause can be specified using the SQL 2008 OFFSET or FETCH FIRST clauses.

Example:

Let’s consider the following Student table.

RollNoNameGrade
12001Aditya9
12002Sahil6
12003Hema8
12004Robin9
12005Sita7
12006Anne10
12007Yusuf7
12008Alex5
SELECT *
 FROM Student
 LIMIT 5; 

The above query will only return the top 5 students from the Student table.

Output:

12001Aditya9
12002Sahil6
12003Hema8
12004Robin9
12005Sita7

The LIMIT clause can also be used with the ORDER BY clause.

SELECT *
 FROM Student
 ORDER BY Grade DESC
 LIMIT 3; 

The above query will return the top 3 students having the highest grades in descending order.

Output:

12006Anne10
12001Aditya9
12004Robin9

The LIMIT operator is especially useful in situations where limited data needs to be retrieved without using any conditional statements.

The LIMIT operator can also be used with the OFFSET operator.

LIMIT with OFFSET

It is to be noted that the OFFSET value should be greater than zero, and it cannot be negative. If a negative value is supplied, it will return an error.

Example:

Let’s consider the same Student table.

RollNoNameGrade
12001Aditya9
12002Sahil6
12003Hema8
12004Robin9
12005Sita7
12006Anne10
12007Yusuf7
12008Alex5
SELECT *
FROM Student
LIMIT 5 OFFSET 2
ORDER BY RollNo;

This query will return the top 5 records of the Student table excluding the top 2 entries.

Output:

RollNoNameGrade
12003Hema8
12004Robin9
12005Sita7
12006Anne10
12007Yusuf7

LIMIT ALL

There is another clause similar to LIMIT known as LIMIT ALL. The LIMIT ALL clause will not put any limit and will return all the entries of the table.

Example:

Let’s again consider the same Student table.

RollNoNameGrade
12001Aditya9
12002Sahil6
12003Hema8
12004Robin9
12005Sita7
12006Anne10
12007Yusuf7
12008Alex5

Query:

SELECT *
FROM Student
LIMIT ALL;
RollNoNameGrade
12001Aditya9
12002Sahil6
12003Hema8
12004Robin9
12005Sita7
12006Anne10
12007Yusuf7
12008Alex5

SQL ORDER BY Multiple Columns

The SQL ORDER BY Multiple Columns will return the data entries in the column by sorting them using multiple columns which have been given in the query.

It will sort the data based on the order of the column names which have been supplied after the ORDER BY clause. The name of the columns should be added in the corresponding order in which the user wants the sorting to happen.

Multiple column names can be added by separating them using a comma (,). Also, the ASC or DESC keywords can be used to specify the order of the sorting.

Example:

Let’s consider the following Employee table having four columns namely id, first_name, last_name, and salary.

idfirst_namelast_namesalary
1LisaUlman3000
2AdaMuller2400
3ThomasGreen2400
4MichaelMuller3000
5MaryGreen2400

Query:

SELECT id,  
 first_name,
 last_name,
 salary
 FROM employee
 ORDER BY salary DESC, last_name; 

Output:

idfirst_namelast_namesalary
4MichaelMuller3000
1LisaUlman3000
3ThomasGreen2400
5MaryGreen2400
2AdaMuller2400

Hence, first, the output has been sorted by salary in descending order and then by last name in ascending order. It is to be noted that if ASC or DESC is not mentioned, it will, by default, sort the both the columns in ascending order.

Conclusion

So, above are some of the most important and frequently used ORDER BY clauses that are used by developers daily in order to perform specific operations on the database and retrieve out data. These clauses can be used individually or they can also be used together, in a combined way, to carry out database operations.



ADVERTISEMENT
ADVERTISEMENT