SQL ORDER BY Clause
For sorting data in ascending and descending order based on one or more columns the SQL ORDER BY clause is used .
SQL ORDER BY syntax:
SELECT expressions
FROM tables
WHERE conditions
ORDER BY expression [ASC | DESC]; For example CUSTOMERS table having the following records:
| ID | NAME | AGE | ADDRESS | SALARY |
| 1 | Himani gupta | 21 | Modinagar | 22000 |
| 2 | Shiv trigune | 22 | Bhopal | 21000 |
| 3 | Ajeet bhandari | 45 | Meerut | 65000 |
This is an example that would sort the result in ascending order by NAME and SALARY.
SELECT * FROM CUSTOMERS
ORDER BY NAME, SALARY; This would produce the following result.
| ID | NAME | AGE | ADDRESS | SALARY |
| 3 | Ajeet bhandari | 45 | Meerut | 65000 |
| 1 | Himani gupta | 21 | Modinagar | 22000 |
| 2 | Shiv trigune | 22 | Bhopal | 21000 |
This is an example to sort the result in descending order by NAME.
SELECT * FROM CUSTOMERS
ORDER BY NAME DESC; This would produce the following result.
| ID | NAME | AGE | ADDRESS | SALARY |
| 2 | Shiv trigune | 22 | Bhopal | 21000 |
| 1 | Himani gupta | 21 | Modinagar | 22000 |
| 3 | Ajeet bhandari | 45 | Meerut | 65000 |
SQL ORDER BY CLAUSE WITH ASCENDING ORDER
This statement is used to sort data in ascending order.
Let us take a CUSTOMERS table having the following records:
| ID | NAME | AGE | ADDRESS | SALARY |
| 1 | Himani gupta | 21 | Modinagar | 22000 |
| 2 | Shiv trigune | 22 | Bhopal | 21000 |
| 3 | Ajeet bhandari | 45 | Meerut | 65000 |
This is an example to sort the result in ascending order by NAME and SALARY.
SELECT * FROM CUSTOMERS
ORDER BY NAME, SALARY; This would produce the following result.
| ID | NAME | AGE | ADDRESS | SALARY |
| 3 | Ajeet bhandari | 45 | Meerut | 65000 |
| 1 | Himani gupta | 21 | Modinagar | 22000 |
| 2 | Shiv trigune | 22 | Bhopal | 21000 |
SQL ORDER BY CLAUSE WITH DESCENDING ORDER:
You should use the DESC attribute in your ORDER BY clause as follows.
Example of an employee table:
| ID | NAME | AGE | ADDRESS | SALARY |
| 1 | Himani gupta | 21 | Modinagar | 22000 |
| 2 | Shiv trigune | 22 | Bhopal | 21000 |
| 3 | Ajeet bhandari | 45 | Meerut | 65000 |
This is an example to sort the result in descending order by NAME.
SELECT * FROM CUSTOMERS
ORDER BY NAME DESC; This would produce the following result.
| ID | NAME | AGE | ADDRESS | SALARY |
| 2 | Shiv trigune | 22 | Bhopal | 21000 |
| 1 | Himani gupta | 21 | Modinagar | 22000 |
| 3 | Ajeet bhandari | 45 | Meerut | 65000 |
SQL ORDER BY RANDOM
If you want the resulting record to be ordered randomly,use the following codes according to several databases.
If you want to fetch random rows from any of the databases you have to use some queries which are altered according to the databases.
Select a random row with MySQL:
If you want to return a random row with MY SQL, Use the following code:
SELECT column FROM table
ORDER BY RAND ()
LIMIT 1 Select a random row with SQL Server:
SELECT TOP 1 column FROM table
ORDER BY NEWID () Select a random row with oracle:
SELECT column FROM
(SELECT column FROM table
ORDER BY dbms_random.value)
WHERE rownum = 1 SQL ORDER BY LIMIT
In SQL order by limit statment used to retrieve limited rows from the database. We can be used in pagination where are forced to show only limited records like 10, 50, 100 etc.
LIMIT CLAUSE FOR ORACLE SQL:
If you want to use LIMIT clause with SQL, you have to use ROWNUM queries because it is used after result are selected.
You should use the following code:
SELECT name, age
FROM
(SELECT name, age, ROWNUM r
FROM
(SELECT name, age, FROM employee_data
ORDER BY age DESC
)
WHERE ROWNUM <=30
)
WHERE r >= 19; This query will give you 19th to 30th rows.