Fetch Top Records Using SELECT Statement

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;

SQL LIMIT MySQL

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:

SQL LIMIT MySQL GROUP BY

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:

SQL LIMIT MySQL ORDER BY

The TOP clause in MS SQL Server

Microsoft SQL Server has the SELECT TOP clause for fetching only top-specified records.

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;

SQL TOP 10

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;

SQL TOP PERCENT

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;

SQL TOP 1

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;

 

 

Author - Atiq Zia

Atiq is the writer at jquery-az.com, an online tutorial website started in 2014. With a passion for coding and solutions, I navigate through various languages and frameworks. Follow along as we unravel the mysteries of coding together!