SQL TOP keyword – Examples

SQL TOP is used to control the amount of rows produced by the SELECT statement. When you are working with big datasets(millions of rows), it is important to keep in mind that SELECT statements use processing power of the system.

SQL TOP Limits the rows returned in a query result set to a specified number of rows or percentage of rows in SQL Server. When you use TOP with the ORDER BY clause, the result set is limited to the first N number of ordered rows. Otherwise, TOP returns the first N number of rows in an undefined order.

NOTE: SQL TOP only works on SQL SERVER. If you want something similar to SQL TOP, you should use SQL LIMIT

Syntax:

SELECT TOP num_of _rows col_1, col_2, col_3
FROM demo_tab ;

num_of_rows specifies the number of rows user wants in the result set.

EXAMPLE:

For demo we are going to use this table CONSOLEDATES

Lets use the TOP keyword

SELECT TOP 10 *
FROM CONSOLEDATES
ORDER BY FirstRetailAvailability ;

SQL TOP WITH PERCENT

SQL TOP WITH PERCENT is used to get the top specified percent of rows. For example suppose you want top 1 percent of the sales , you can use SELECT TOP 1 PERCENT to get that data.

Syntax:

SELECT TOP num_of_percent PERCENT
FROM demo_tab ;

Example:

For example we are going to use a big table with ~16000 rows. The table name is ‘CONSOLEGAMES’

Now lets see top 1 percent of NA_SALES

SELECT TOP 1 PERCENT
NAME,PLATFORM, NA_SALES
FROM CONSOLEGAMES ;

After using the SELECT TOP 1 PERCENT we only got 160 rows in result set.

SQL TOP WITH TIES

SQL TOP with TIES are used to keep the extra rows if they have similar values.

For Example, Suppose you want to find out the top 3 highest paid employees of your foundation but do not want to skip the employees that have similar salaries with any of the top 3. The con of using SQL TOP WITH TIES is that you may exceed the number of rows you want.

Syntax:

SELECT TOP num_of _rows WITH TIES
COL_1, COL_2
FROM demo_tab
ORDER BY COL_1 ;

num_of _rows represent the number of rows user want in result set.
Keep in mind ORDER BY clause is important to specify.

EXAMPLE:

we are going to use the table ‘EMP’ for example-

SELECT TOP 3 WITH TIES
*
FROM EMP
ORDER BY SALARY DESC ;

There are 4 rows instead of 3 because ‘Rohit’ and ‘Stacy’ have similar salaries.

Thanks For Reading!

Related Posts