Select Top 1 * from MyTable is nothing new. I was extremely confident with my knowledge of TOP statements, I made the critical mistake of not preparing a set of tested TOP SQL statements for my last week MSSQL class. I can produce a TOP statement query on the fly and explain my students everything there to know about it, so I thought. After all it is just TOP. I stumped. I totally stumped front of “TOP With Ties”. After all there is more to TOP than just getting n amount of TOP records.

Let’s create a temporary table with two columns with person’s name and age.

   1: CREATE TABLE #MyTable (Name varchar(20), age INT)
   2: INSERT INTO #MyTable
   3: SELECT 'Lai', 20 UNION ALL
   4: SELECT 'Simson', 20 UNION ALL
   5: SELECT 'Ted', 20 UNION ALL
   6: SELECT 'Hajek', 19 UNION ALL
   7: SELECT 'Rosy', 19 UNION ALL
   8: SELECT 'Shona', 19 UNION ALL
   9: SELECT 'Loris', 18 UNION ALL
  10: SELECT 'Billye', 17
Show/Hide Line Numbers . Full Screen . Plain

   1: SELECT TOP(1) * FROM #MyTable


SQL server grabs the first record it can get its hand on and returns it to us.

   1: SELECT TOP(1) * FROM #MyTable ORDER BY age desc

Now sorting kicks in and it returns the First record SQL server can get its hand on after sorting.

Now here comes the Top With Ties.

   1: SELECT TOP(1) WITH TIES * FROM #MyTable ORDER BY age desc


This returns 3 records. Why? Because WITH TIES grabs all other records that matches the value of “order by” column. We have three people of age 20, SQL grabs the first record and now it have to grab all other 20 year olds alone with it too.

   1: SELECT TOP(2) WITH TIES * FROM #MyTable ORDER BY age desc


This returns 3 same records, because we are asking for 2 records and we happened to have 3 records already.

   1: SELECT TOP(4) WITH TIES * FROM #MyTable ORDER BY age desc


This returns 6 records, because after SQL server done with first three 20 year olds, it grabs the 4th one, a 19 year old. But we happened to have 3 more 19 year olds in our table. WITH TIES forces it to grab them all.

TOP WITH TIES makes SQL server grab the first record and all other similar records regardless the TOP number of rows we asked for. If record count is less than our requested TOP value, it goes to the next position and grabs all rows similar to that. It do that until it matches or exceed the TOP number.

1 Comment :
Mark Gregory
Thursday 05 December 2013 08:10 PM
Nice little find! I always forget about With Ties until days after I really needed it.