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.
Show/Hide Line Numbers . Full Screen . Plain
SQL server grabs the first record it can get its hand on and returns it to us.
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.
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.
This returns 3 same records, because we are asking for 2 records and we happened to have 3 records already.
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.
