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
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.
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.
SELECT TOP num_of_percent PERCENT FROM demo_tab ;
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.
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.
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!
Read More : Learn SQL Fast