Use the TOP Filter's WITH TIES Option

When I teach workshops on T-SQL and I get to the topic of the TOP filter, I often encounter surprised faces upon mention of the WITH TIES option. Apparently, many people don't know that this option exists or what it does—even though it has been available with the TOP filter since SQL Server 7.0.

Consider the following query:

SELECT TOP (3) orderid, orderdate, empid, custidFROM dbo.OrdersORDER BY orderdate DESC;

This query returns the three most recent orders. However, because the orderdate column isn't unique, there's no preference between rows with the same date. This fact makes the query nondeterministic. Between rows with the same date, physical access order determines which order gets returned. The WITH TIES option means that instead of stopping immediately after the requested number of rows is returned, the query will also give you all ties with the last row based on the ordering elements (orderdate, in our case). This means that you might get more than the number of rows that you requested, but the selection of rows becomes deterministic.

Here's how you implement this option with our query:

SELECT TOP (3) WITH TIES orderid, orderdate, empid, custidFROM dbo.OrdersORDER BY orderdate DESC;

Now that you know what the WITH TIES option does, here's a small brainteaser for you. Try to figure out what the following query does before you run it.

SELECT TOP (1) WITH TIES orderid, orderdate, custid, empid, fillerFROM dbo.OrdersORDER BY  ROW_NUMBER() OVER(PARTITION BY custid ORDER BY orderdate DESC, orderid DESC);

Now, run the following code to get the answer:

SELECT CAST(0x5468652071756572792072657475726E7320746865206D6F737420726563656E74206F7264657220666F72206561636820637573746F6D65722E AS VARCHAR(1000));

Were you correct?