SELECT TOP .. WITH TIES

The SQL Server TOP Expression returns only the first set of rows that is either a number or a percent of rows. If you want additional rows to be returned from the base result set if duplicate values exists in the qualified set of results, then use TOP..WITH TIES as shown in the example below:

   1: DECLARE @Sales TABLE 

   2: (

   3:   Customer NVARCHAR(200),

   4:   TotalSum FLOAT

   5: )

   6:  

   7: INSERT INTO @Sales

   8:   (Customer,  TotalSum)

   9: VALUES

  10:   ('Vijay', 4000000),

  11:   ('Ajay', 1000000),

  12:   ('Mehul', 2000000),

  13:   ('Kalpesh', 1000000),

  14:   ('Jatin', 20)

  15:  

  16: SELECT 

  17:   TOP (3) *

  18: FROM 

  19:   @Sales

  20: ORDER BY 

  21:   TotalSum DESC

I have written Top (3) in above query so it is confirm that result will be only 3 rows.
Output:


But what if i want to know that how many customers are there that having top 3 sales in above query "Mr. Ajay" is missing though he is also having TotalSum = 1000000.
So in this situations we can use "TOP (n) WITH TIES" sql statement to get the result.

   1: SELECT 

   2:   TOP (3) WITH TIES *

   3: FROM 

   4:   @Sales

   5: ORDER BY 

   6:   TotalSum DESC

Output:


Note : TOP [WITH TIES] is always use with ORDER BY only.
Now, understand this query in depth,
what is the scenario if record set is like below.

Output:


Now run the same query that we have run above.

   1: SELECT 

   2:   TOP (3) *

   3: FROM 

   4:   @Sales

   5: ORDER BY 

   6:   TotalSum DESC

Output:

   1: SELECT 

   2:   TOP (3) WITH TIES *

   3: FROM 

   4:   @Sales

   5: ORDER BY 

   6:   TotalSum DESC

Output:


Conclusion :
From above example it is clear that TOP [WITH TIES] repeat only those record which is available in basic record set that is TOP (n) and that is fully depend on ORDER BY Column.