Friday, 18 May 2012

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:
image

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:
image

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:
image

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:
image
   1: SELECT 
   2:   TOP (3) WITH TIES *
   3: FROM 
   4:   @Sales
   5: ORDER BY 
   6:   TotalSum DESC
Output:
image

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.

No comments:

Post a comment