Hit enter after type your search item

How to get top records from the SELECT statement?

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 as using the SELECT statement. Different databases like MS SQL Server, MySQL and Oracle have different clauses for getting top records.

See the next section for retrieving top records in different databases.

The LIMIT in MYSQL database

For getting the top rows from tables, you may use the LIMIT keyword in MySQL database. A few examples of its usage are shown below.

Fetching top 3 rows by LIMIT clause

This example fetches top 3 rows from the MySQL database table by using LIMIT clause:

The SELECT – LIMIT query:


SQL LIMIT MySQL

Using WHERE clause with SELECT – LIMIT

This is how the LIMIT clause is used with the WHERE clause in MySQL table:

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 GROUP BY clause. Two queries are executed; one without the LIMIT clause to get all rows and the other with the LIMIT clause. Have a look at dummy data output:

The queries with SUM/GROUP BY and LIMIT:

Query 1:


Query 2:

The result with our table:

SQL LIMIT MySQL GROUP BY

The example with 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:


With Limit

The result:

SQL LIMIT MySQL ORDER BY

The TOP clause in MS SQL Server

The 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 top 10 records by using the SELECT TOP statement. I used employee table that contains only a few records for the demo only:

The Query:


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:


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 TOP 1 record is retrieved:

Queries:


SQL TOP 1

The first query retrieves records without the TOP 1 clause and the second with it.

The example of using ROWNUM in Oracle database

Similarly, you may use the ROWNUM in the Oracle database for getting the top rows from table data. The following query shows how you may use that:

The ROWNUM query in Oracle:


 

 

This div height required for enabling the sticky sidebar