SQL SELECT TOP

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..

SQL Order By

SQL Group By

SQL Having

SQL Over Clause

SQL Distinct

SQL Where

SQL Case Statement

SQL Basics TutorialSQL Advance TutorialSSRSInterview Q & A
SQL Create tableSQL Server Stored ProcedureCreate a New SSRS Project List Of SQL Server basics to Advance Level Interview Q & A
SQL ALTER TABLESQL Server MergeCreate a Shared Data Source in SSRSSQL Server Question & Answer Quiz
SQL DropSQL Server PivotCreate a SSRS Tabular Report / Detail Report
..... More.... More....More
Power BI TutorialAzure TutorialPython TutorialSQL Server Tips & Tricks
Download and Install Power BI DesktopCreate an Azure storage accountLearn Python & ML Step by stepEnable Dark theme in SQL Server Management studio
Connect Power BI to SQL ServerUpload files to Azure storage containerSQL Server Template Explorer
Create Report ToolTip Pages in Power BICreate Azure SQL Database ServerDisplaying line numbers in Query Editor Window
....More....More....More




 331 total views,  3 views today

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.