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


Comments

No responses found. Be the first to comment...


  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: