SQL SELECT TOP statement is used to retrieve records from tables and limit the number of rows or percentage of rows returned based on a specified value.
It is also known as TOP Clause or TOP keyword.
TOP return the n number of rows in an undefined order. So using Order By clause with TOP, returns theย n number of rows in specified sort order that can be either Asc or Desc.
SYNTAX
[ TOP (expression) [PERCENT] [ WITH TIES ] ]
Expression
Is a numeric expression that defines the number of rows to be returned.
PERCENT
Is a value which indicates that the query return the percentage number of rows from total rows in the result set. Fractional values are rounded up to the next integer value.
WITH TIES
Returns two or more rows that tie for last place in the limited results set. It is always used with the ORDER BYย clause.ย WITH TIESย might cause more rows to be returned than the value specified inย expression.
Assume that, the specified value of expression is 3 but two additional rows match the values of the ORDER BY columns for third row, then in this case using the TOP with WITH TRIES clause, adds the additional value that have same match with third row value and in result of that total 5 rows are returned.
Lets create a sample table and insert some records into this table.
CREATE TABLE dbo.OfficeSupplies ย ( Item_Id INT PRIMARY KEY, ย Name VARCHAR(100), ย Quantity INT, ย Amount NUMERIC(8,2) ) INSERT INTO dbo.OfficeSupplies (Item_Id, Name, Quantity, Amount) VALUES (1, 'Furnitures', 500, 150000), (2, 'A-4 Size Papers', 150, 34000), (3, 'Pen Drive', 600, 35000), (4, 'Printer', 30, 150000), (5, 'Keyboard', 1550, 20000), (6, 'Hard Disk', 5000, 75000), (7, 'Laptop- HP', 3400, 150000), (8, 'Round Table', 150, 178000), (9, 'White Board', 180, 120000), (10, 'CPU', 250, 240000)
Now we have a table named OfficeSupplies as shown below.
SELECT * FROM dbo.OfficeSupplies
Basic use of TOP Clause
Lets select the top 5 records from table OfficeSupplies based on highest quantity value.
SELECT TOP 5 * FROM dbo.OfficeSupplies ORDER BY Quantity DESC
You can see, it returns the top 5 records from table based on highest quantity value.
You will notice that, it returns all the columns from table. If you do not want all columns from table then you can specify the relevant column as shown below.
Lets select only Name, Quantity from above TOP statement.
Using TOP Clause with PERCENT
Using a PERCENT with TOP clause, indicates that the query return the n PERCENT of rows from total rows in the result set.
If value after evaluating n percent of rows from total rows comes in decimal then fractional values are rounded up to the next integer value.
Lets select TOP 50 percent of rows from all the rows in table OfficeSupplies based on highest Amount value.
SELECT TOP 50 PERCENT * FROM dbo.OfficeSupplies ORDER BY Amount DESC
[Also Read: SQL Order By]
As you can see, it returns top 50 percent of rows from table based on highest amount value which are the 50 percent rows of total rows.
You can see there are total 10 rows in OfficeSupplies table,
Therefore the 50 percent of rows will be 50% * 10 rows => 50/100*10 = 5 rows.
Using TOP clause with TIES
Assume that, you want to see the top 3 expensive items that you have purchased for your office, obviously you will select top 3 Item based on the highest amount value.
Lets select select the top 3 records from table based on highest amount value.
SELECT TOP 3 * FROM dbo.OfficeSupplies ORDER BY Amount DESC
You can see, it returns top 3 Items which have highest purchased amount value.
But if you take a look on entire records in table for amount value 150000 that is third highest purchased Item in result set, you will get to know that there are 2 more items in table which also have same amount value that is 150000.
SELECT * FROM dbo.OfficeSupplies
The reason is that of returning only 3 records instead of total 5 records is that TOP clause returns exactly the same number of rows that you specify with TOP.
But in case, if you want to see all the records that tie for last place in the limited results set, you can use TOP with the WITH TIES clause.
SELECT TOP 3 WITH TIES * FROM dbo.OfficeSupplies ORDER BY Amount DESC
You can see, even though you have specified top 3 in select statement yet it returns 5 records from table. And the reason is that the third highest value that is 150000 in top result set is tied with 2 more records that have same value, so the WITH TIES clause adds all the records that have same value, that’s why total 5 rows are returned.
It is must to use WITH TIES clause with Order By Clause otherwise you will get an error as shown below.
Lets run the above T-SQL without specifying Order By clause.
You can see, it returns an error.
Also Read..
2,246 total views, 1 views today