- 4 years ago
- Zaid Bin Khalid
- 11081 Views
-
4
In this session, you will learn how to retrieve the fixed number of records from the table in SQL.
SQL TOP and LIMIT Clause.
The TOP clause is used to specify the number of records to return and The LIMIT clause statement is used to retrieve the records from one or more tables from a SQL database and limit the number of records that based on a limit value.
Limiting Result Sets.
In some cases, you may not be liked in all of the rows returned by a query, just suppose, if you just want to retrieve the top 10 employees who recently joined the organization, get top 3 students by score, or something like this.
To deal with such types of situations, you can use the SQL TOP clause in your SELECT statement. The TOP clause is only supported by SQL Server and MS Access database systems.
SQL TOP Syntax.
The basic syntax for the SQL TOP clause as mention below.
SELECT TOP number | percent column_list FROM table_name;
Suppose we have a table called employee table in our database with the following records.
+--------+--------------+------------+--------+---------+
| emp_id | emp_name | hire_date | salary | dept_id |
+--------+--------------+------------+--------+---------+
| 1 | Ethan Hunt | 2001-05-01 | 5000 | 4 |
| 2 | Tony Montana | 2002-07-15 | 6500 | 1 |
| 3 | Sarah Connor | 2005-10-18 | 8000 | 5 |
| 4 | Rick Deckard | 2007-01-03 | 7200 | 3 |
| 5 | Martin Blank | 2008-06-24 | 5600 | NULL |
+--------+--------------+------------+--------+---------+
The following statement returns the top three highest-paid employees from the employee’s table.
SELECT TOP 3 * FROM employees ORDER BY salary DESC;
The output set returned will look something like this.
+--------+--------------+------------+--------+---------+
| emp_id | emp_name | hire_date | salary | dept_id |
+--------+--------------+------------+--------+---------+
| 3 | Sarah Connor | 2005-10-18 | 8000 | 5 |
| 4 | Rick Deckard | 2007-01-03 | 7200 | 3 |
| 2 | Tony Montana | 2002-07-15 | 6500 | 1 |
+--------+--------------+------------+--------+---------+
MySQL LIMIT Syntax.
The MySQL LIMIT clause does the same work as the SQL TOP clause. Its basic syntax is.
SELECT column_list FROM table_name LIMIT number;
The mention below statement returns the top three highest-paid employees from the employee’s table.
SELECT TOP 30 PERCENT * FROM employees ORDER BY salary DESC;
After the execution of the statement, you will get the output result as shown below.
+--------+--------------+------------+--------+---------+
| emp_id | emp_name | hire_date | salary | dept_id |
+--------+--------------+------------+--------+---------+
| 3 | Sarah Connor | 2005-10-18 | 8000 | 5 |
| 4 | Rick Deckard | 2007-01-03 | 7200 | 3 |
+--------+--------------+------------+--------+---------+
Setting Row Offset in LIMIT Clause.
The LIMIT clause only accepts an optional second parameter.
When two parameters are defined, the first parameter will define the offset of the first row to return for example the starting point, The second parameter will define the maximum number of rows to return.
Now, if you want to find out the third-highest paid employee, you can execute the following statement.
SELECT * FROM employees ORDER BY salary DESC LIMIT 2, 1;
After executing the above command, you will get only one record in your result set.
+--------+--------------+------------+--------+---------+
| emp_id | emp_name | hire_date | salary | dept_id |
+--------+--------------+------------+--------+---------+
| 2 | Tony Montana | 2002-07-15 | 6500 | 1 |
+--------+--------------+------------+--------+---------+
- 4 years ago
- Zaid Bin Khalid
- 11081 Views
-
4