While studying for my SQL Server exam recently I came across an interesting feature of the T-SQL SELECT statement I'd never noticed before - the WITH TIES argument. Used in conjunction with a TOP n or TOP n PERCENT argument, and the ORDER BY clause, you can return not just the n records you'd expect, but all additional records with the same value in the ORDER BY column as the nth record.
Lets say, for example, that you have an online competition and you want to pick the top 5 entrants who achieved the highest scores. Your data might include the following entries:
Score Name
285 Bob
250 Bill
240 Mick
240 Sue
235 James
235 Jill
235 Dean
230 Michelle
In the past I would of just written:
SELECT TOP 5 * FROM tablename ORDER BY score DESC
However, how would this decide between James, Jill and Dean who are in join 5th place? It would be much better to include those entries who are in joint 5th position (unless, of course, you also decide to order the results by the date entered so the first person to get the score wins - but let's say we don't!). Instead of the above, you can write the query as:
SELECT TOP 5 WITH TIES * FROM tablename ORDER BY score DESC
This will return not just the top 5 records, but also additional records with the same value as the same score as the 5th record. In this example, it will return all records from Bob to Dean - 7 records in total.