Categories
SQL

Why TOP (1) Can Return Multiple Rows (and Why That Matters)

Having TOP (1) return multiple rows feels wrong… but that’s what WITH TIES can do.

For a long time I used patterns like this to get the first record in a group:

WITH LatestPosts AS (
    SELECT OwnerUserId,
        Score,
        RowNum = ROW_NUMBER() OVER (
            PARTITION BY OwnerUserId
            ORDER BY CreationDate DESC)          
    FROM dbo.Posts
)
SELECT OwnerUserId, Score
FROM LatestPosts
WHERE RowNum = 1;

Then I found the cleaner alternative:

SELECT TOP (1) WITH TIES
    OwnerUserId, Score
FROM dbo.Posts
ORDER BY ROW_NUMBER() OVER (
    PARTITION BY OwnerUserId
    ORDER BY CreationDate DESC);

Let’s dive into what it is, and why you (probably) shouldn’t use it.

WITH TIES

The WITH TIES syntax was added over 20 years ago but it was something I hadn’t stumbled across until recently.

It allows more records to be returned than specified by the TOP (x) if there are multiple tied for the last spot based on the ORDER BY. For example if you ask for TOP (10) and records 10, 11, and 12 all share the same sort value, you’ll get 12 records back.

In this instance we’re using the ROW_NUMBER function partitioned by OwnerUserId so there will be 1 or more rows for every user. Coupled with the TOP (1), we’re effectively forcing one row per user as their first records are all tied.

We’re still using the same window function, and we’re still only taking the first record per user. But the syntax is much more readable. Is it the perfect solution?

Trade-offs

As with all good things, we have trade-offs. Unfortunately this case has a performance tradeoff.

Here are plans for the above queries:

Query plan when a regular CTE is used, only requiring a single Sort operator
Query plan when using the WITH TIES syntax, with two Sort operators required

Ugh, a second Sort operator.

The first Sort operator and Window Aggregate are performing the same tasks. They’re sorting based on the window function and applying the ROW_NUMBER function. Then things differ. The first query simply filters the results based on row 1. The second query re-sorts the results ready to allow the TOP operator to do its work.

The optimiser doesn’t know how many records are needed to satisfy WITH TIES, so we have to allow for a full sort, coupled with increased data volume with the result of the ROW_NUMBER() function (increasing each record from 8 > 16 bytes).

This means a larger memory grant for the query, plus the added CPU of sorting as much of the data is needed. The impact from ~3.5mil records with the queries above was +200% memory grant and +25% CPU time.

So as my general rule of thumb

  • For a small dataset as part of a larger query where the brevity may be beneficial, go for it
  • For larger datasets or busy systems, I wouldn’t recommend this approach. This data will likely grow and the cost for the simplicity will outweigh its benefits

Wrap up

In this post we’ve demonstrated the WITH TIES clause for the TOP operator, which we can use to replace a semi-common CTE pattern.

Given the performance impact shown here, I understand why it wouldn’t be common place. It may have uses for minor datasets or ad-hoc interrogation, but generally it would be best to avoid. If you’re doing any filtering up front, an APPLY may be a better approach (another post for another day).

With that said, I hadn’t stumbled upon this syntax until this year and still find it interesting. It’s better to read, but slower to run. I guess you could call it paying the syn-tax (heh).

Leave a comment