Top with ties function in SQL
In this article, I am going to explain the use of TOP WITH TIES function and how to use it. If we want to select all the rows, with same values we have to include TOP WITH TIES option in the query statement.
Normally we are using TOP function in select statement to select the TOP Value records.
For example, your table have 100 rows with 50 rows have same value. If you use TOP 5, it will return only 5 rows.
But if you use TOP WITH TIES function it will return all the rows with same value.
When you using the TOP WITH TIES function, you must be use with the ORDER BY clause.
Here i am explained with temp table and sample data.
Table structure:
CREATE TABLE #Temp
(
Id INT,
Name Varchar(20),
Marks BIGINT
)
Insert table data with Same Value:
INSERT INTO #Temp SELECT 1,'AAA',10
INSERT INTO #Temp SELECT 2,'BBB',20
INSERT INTO #Temp SELECT 3,'CCC',30
INSERT INTO #Temp SELECT 4,'DDD',10
INSERT INTO #Temp SELECT 4,'EEE',20
INSERT INTO #Temp SELECT 4,'FFF',10
Using TOP Function:
SELECT TOP 1 Name, Marks FROM #Temp ORDER BY Marks
Using TOP WITH TIES function:
SELECT TOP 1 WITH TIES Name, Marks FROM #Temp ORDER BY Marks