SQL Select Top

Introduction to SQL Select Top

We can retrieve the resultset from the queries by specifying the number of the records that can be maximumly retrieved from the query using the top statement in SQL. When dealing with databases that are huge and involve queries on the tables that involve a huge set of records and the resultant resultset also includes too many records such that it becomes heavy for the system to handle and can even result to the system hanging for a long duration or sometimes crash, the limited number of the records can be retrieved from the query which reduces the loads on the system.

We can specify the limit using the top statement in SQL. Though, many DBMS does not support the usage of top statements such as MySQL and oracle. They provide an alternative way like the LIMIT clause and ROWNUM in MySQL and Oracle respectively. In this article, we will study the general syntax of the TOP clause in SQL and see its usages and implementation with the help of certain examples.

Syntax:

The syntax of the select top is as shown below –

SELECT TOP (numeric_expression) [PERCENT] [WITH TIES] FROM
name_of_table
ORDER BY
name_of_column;

The numeric expression can be the number of the rows to be retrieved in the final resultset after the execution of the query statement. The use of the PERCENT keyword is optional and when specified the numeric expression is considered as the float value that specifies the percentage of the result set that should be retrieved instead of the row count. When PERCENT is not used the numeric expression is the BIGINT value that specifies the number of records to be retrieved from the select query. The name_of the table is the table name from which the records are to be retrieved while the name of the column is the column name based on which the result should be sorted.

The use of the ORDER BY clause in the select top is optional. However, it is a good practice to use the TOP statement along with the order by clause as it will make the query retrieve the ordered resultset containing the limited amount of records that occur first in the order sequence.

The WITH TIES clause is also optional which mentions that all the related records of the ordered result set should also be retrieved. The use of WITH TIES can increase the retrieved result set count than specified by the numeric expression. For example, if we are trying to retrieve the most scored person in the class and you are making the use of TOP 1 then if you are not using WITH LIES the result set will give only one record even if the same scored persons exist that have achieved the highest scores. And when using TOP1 along with the “WITH LIES” clause, it will result in retrieval of all the records having the highest scores in the class.

Examples of SQL Select Top

Let us consider one existing table named educba_articles in our database named educba. The contents and the structure of the table can be retrieved by executing the following query –

SELECT * FROM `educba_articles`;

The execution of the above query statement gives an output which is as shown below.

Now, we can observe that the table educba_articles consists of 20 records in it. We want to retrieve only the first 10 records that have the maximum amount of rate assigned to them. For this, we can make the use of the select top clause in SQL using the following query statement which retrieves the name, author, and the rate of the article.

SELECT TOP 10
NAME,
author,
rate
FROM
educba.`educba_articles`
ORDER BY rate DESC ;

The execution of the above query statement gives an output which is as shown below.

Use of WITH TIES clause

Suppose that we have the top most six records of the articles listed according to their rates and we have to find the highest-rated articles out of them. If we simply use the top statement as shown in the above query statement, we will use the following query.

SELECT TOP 6
NAME,
author,
rate
FROM
educba.`educba_articles`
ORDER BY rate DESC ;

The execution of the above query statement gives an output which is as shown below.

But, we can observe that two more articles are rated the same as the rates of the retrieved records integer and wherein the table educba_article. As we want to retrieve the highest-paid records that lie in the top six positions they should also be retrieved as they also have the same rate assigned to it. This problem can be resolved simply by using the WITH TIES in the same query statement as shown below.

SELECT TOP 6 WITH TIES
NAME,
author,
rate
FROM
educba.`educba_articles`
ORDER BY rate DESC ;

The execution of the above query statement gives an output which is as shown below –

Here, NOT NULL and where named articles are retrieved because of the usage of WITH TIES in the top clause in our select query statement.

The use of PERCENT

Suppose that we want to retrieve the only 1/4th of the resultset of the query statement. For such cases, we can make the use of the PERCENT clause. The 1/4th amount of anything translates to 25% off that thing. Hence, by mentioning the 25 PERCENT in the top clause we can retrieve 1/4th of records of the original query resultset. As our table contains 20 records in it. The 25 percent of the records of 20 records will be 5 records. Hence the use of the following query statement should result in the first five records being retrieved that are ordered based on their rates in a descending manner.

SELECT TOP 25 PERCENT
NAME,
author,
rate
FROM
educba.`educba_articles`
ORDER BY rate DESC ;

The execution of the above query statement gives an output which is as expected and shown below.

Conclusion

The use of SELECT TOP statement in SQL helps in limiting the number of records that will be retrieved from the query statement in SQL. Many DBMS such as MySQL and oracle does not support its usage but provide alternatives like LIMIT and ROWNUM which can be used for limiting the resultset.

Recommended Articles

This is a guide to SQL Select Top. Here we also discuss the introduction and syntax of sql select top along with different examples and its code implementation. You may also have a look at the following articles to learn more –

1. MySQL CEIL
2. SQL Virtual Table
3. MySQL Sync
4. SQL Pattern Matching