How to fetch top records in SQL?
If you have large tables with thousands of records then querying whole data may cause overheads on the server, especially for many concurrent connections.
The SQL has different clauses for fetching top records while using the SELECT statement.
Different databases like MS SQL Server, MySQL, and Oracle have different clauses for getting top records.
--MySQL Examples SELECT * FROM boot_table LIMIT 3; --MS SQL Server SELECT TOP 10 id, emp_name, emp_age, emp_salary FROM sto_employees; --Oracle SELECT id, emp_name, emp_age, emp_salary FROM sto_employees WHERE ROWNUM <= 10;
See the next section for retrieving top records in different databases.
The LIMIT in the MYSQL database
To fetch the top rows from tables, you may use the LIMIT keyword in the MySQL database. A few examples of its usage are shown below.
Fetching top 3 rows by LIMIT clause
This example fetches the top 3 rows from the MySQL database table by using the LIMIT clause:
The SELECT – LIMIT query:
SELECT * FROM boot_table LIMIT 3;
Using WHERE clause with SELECT – LIMIT
This is how the LIMIT clause is used with the WHERE clause in the MySQL table:
SELECT Product_Name, Quantity FROM boot_table WHERE Quantity = '100 Bags' LIMIT 1;
How to use LIMIT with GROUP BY clause
You may also limit the rows retrieved with grouped data.
In this example, the SUM aggregate function is used with the GROUP BY clause.
Two queries are executed:
- One without the LIMIT clause to get all rows.
- The second with the LIMIT clause.
Have a look at the dummy data output:
The queries with SUM/GROUP BY and LIMIT:
Query 1:
SELECT Product_name, SUM(quantity) As 'Total Quantity' FROM boot_table GROUP BY Product_name;
Query 2:
SELECT Product_name, SUM(quantity) As 'Total Quantity' FROM boot_table GROUP BY Product_name Limit 2;
The result in our table:
The example with the ORDER BY clause
If you intend to use the ORDER BY clause for sorting the results and then getting the top rows by LIMIT clause then you can do this as follows.
Notice the table rows with and without using the LIMIT clause:
The queries with and without LIMIT:
SELECT Product_name, SUM(quantity) As 'Total Quantity' FROM boot_table GROUP BY Product_name ORDER BY SUM(quantity) DESC;
With Limit
SELECT Product_name, SUM(quantity) As 'Total Quantity' FROM boot_table GROUP BY Product_name ORDER BY SUM(quantity) DESC Limit 2;
The result:
The TOP clause in MS SQL Server
You may specify the number of records or percentage as shown in the examples below.
The example of SELECT TOP 10 records in SQL Server
The following query retrieves the top 10 records by using the SELECT TOP statement. I used an employee table that contains only a few records for the demo only:
The Query:
SELECT TOP 10 id, emp_name, emp_age, emp_salary FROM sto_employees;
Using PERCENT with TOP query example
You may use the PERCENT keyword with the SELECT TOP statement for retrieving the percentage of top records as shown in the following query:
The PERCENT query:
SELECT TOP 30 PERCENT id, emp_name, emp_age, emp_salary FROM sto_employees;
TOP 1 record with WHERE clause example
Now, have a look at the example where a condition is specified in the WHERE clause and only the TOP 1 record is retrieved:
Queries:
SELECT * FROM sto_employees WHERE emp_salary > 3500; SELECT TOP 1 id, emp_name, emp_age, emp_salary FROM sto_employees;
The first query retrieves records without the TOP 1 clause and the second with it.
ROWNUM in the Oracle database
Similarly, you may use the ROWNUM in the Oracle database to get the top rows from table data. The following query shows how you may use that:
The ROWNUM query in Oracle:
SELECT id, emp_name, emp_age, emp_salary FROM sto_employees WHERE ROWNUM <= 10;