SQL TOP Clause
The SQL TOP clause is used to fetch a TOP N number or X percent records from a table.
Note: All the databases do not support TOP clause. For example MySQL supports LIMIT clause to fetch limited number of records and Oracle uses ROWNUM to fetch limited number of records.
Syntax:
The basic syntax of TOP clause with SELECT statement would be as follows:
SELECT TOP number|percent column_name(s)
FROM table_name
WHERE [condition]
Example:
Consider the CUSTOMERS table having the following records:
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramakrishna | 32 | Ahmedabad | 2000.00 |
| 2 | Mdhu sudhan rao | 25 | Delhi | 1500.00 |
| 3 | Sai | 23 | Kota | 2000.00 |
| 4 | Kishor | 25 | Mumbai | 6500.00 |
| 5 | Ranjith | 27 | Bhopal | 8500.00 |
| 6 | Abhi | 22 | MP | 4500.00 |
| 7 | Kumar | 24 | Indore | 10000.00 |
+----+----------+-----+-----------+----------+
Following is an example on SQL server, which would fetch top 3 records from CUSTOMERS table:
SQL> SELECT TOP 3 * FROM CUSTOMERS;
This would produce the following result:
+----+---------+-----+-----------+---------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+---------+-----+-----------+---------+
| 1 | Ramakrishna | 32 | Ahmedabad | 2000.00 |
CHAPTER 20
20
| 2 | Mdhu sudhan rao | 25 | Delhi | 1500.00 |
| 3 | Sai | 23 | Kota | 2000.00 |
+----+---------+-----+-----------+---------+
If you are using MySQL server, then here is an equivalent example:
SQL> SELECT * FROM CUSTOMERS
LIMIT 3;
This would produce the following result:
+----+---------+-----+-----------+---------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+---------+-----+-----------+---------+
| 1 | Ramakrishna | 32 | Ahmedabad | 2000.00 |
| 2 | Mdhu sudhan rao | 25 | Delhi | 1500.00 |
| 3 | Sai | 23 | Kota | 2000.00 |
+----+---------+-----+-----------+---------+
If you are using Oracle server, then here is an equivalent example:
SQL> SELECT * FROM CUSTOMERS
WHERE ROWNUM <= 3;
This would produce the following result:
+----+---------+-----+-----------+---------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+---------+-----+-----------+---------+
| 1 | Ramakrishna | 32 | Ahmedabad | 2000.00 |
| 2 | Mdhu sudhan rao | 25 | Delhi | 1500.00 |
| 3 | Sai | 23 | Kota | 2000.00 |
+----+---------+-----+-----------+---------+
SQL ORDER BY Clause
The SQL ORDER BY clause is used to sort the data in ascending or descending order, based on one or more columns. Some database sorts query results in ascending order by default.
Syntax:
The basic syntax of ORDER BY clause 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 whatever column you are using to sort, that column should be in column-list.
Example:
Consider the CUSTOMERS table having the following records:
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramakrishna | 32 | Ahmedabad | 2000.00 |
| 2 | Mdhu sudhan rao | 25 | Delhi | 1500.00 |
| 3 | Sai | 23 | Kota | 2000.00 |
| 4 | Kishor | 25 | Mumbai | 6500.00 |
| 5 | Ranjith | 27 | Bhopal | 8500.00 |
| 6 | Abhi | 22 | MP | 4500.00 |
| 7 | Kumar | 24 | Indore | 10000.00 |
+----+----------+-----+-----------+----------+
Following is an example, which would sort the result in ascending order by NAME and SALARY:
SQL> SELECT * FROM CUSTOMERS
ORDER BY NAME, SALARY;
This would produce the following result:
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 4 | Kishor | 25 | Mumbai | 6500.00 |
| 5 | Ranjith | 27 | Bhopal | 8500.00 |
| 3 | Sai | 23 | Kota | 2000.00 |
| 2 | Mdhu sudhan rao | 25 | Delhi | 1500.00 |
| 6 | Abhi | 22 | MP | 4500.00 |
| 7 | Kumar | 24 | Indore | 10000.00 |
| 1 | Ramakrishna | 32 | Ahmedabad | 2000.00 |
+----+----------+-----+-----------+----------+
Following is an example, which would sort the result in descending order by NAME:
SQL> SELECT * FROM CUSTOMERS
ORDER BY NAME DESC;
This would produce the following result:
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramakrishna | 32 | Ahmedabad | 2000.00 |
| 7 | Kumar | 24 | Indore | 10000.00 |
| 6 | Abhi | 22 | MP | 4500.00 |
| 2 | Mdhu sudhan rao | 25 | Delhi | 1500.00 |
| 3 | Sai | 23 | Kota | 2000.00 |
| 5 | Ranjith | 27 | Bhopal | 8500.00 |
| 4 | Kishor | 25 | Mumbai | 6500.00 |
+----+----------+-----+-----------+----------+
