I have been working with SQL Server for quite a few years now, and it still happens quite often that I discover new cool things to do in t-sql. My latest discovery is not a new feature at all, because it was introduced back in SQL Server 2005 – but for some reason I haven’t come across it before. So I thought I might share it with you

Consider the following example:

CREATE TABLE AggregatedClientRequests
(
    Id INT IDENTITY PRIMARY KEY,
    ClientIp VARCHAR(200) NOT NULL,
    NumOfRequests INT NOT NULL
)
GO
INSERT INTO AggregatedClientRequests (ClientIp, NumOfRequests)
VALUES
    ('1.1.1.1', 5100),
    ('2.2.2.2', 10000),
    ('3.3.3.3', 200),
    ('3.3.3.3', 44000),
    ('4.4.4.4', 2200),
    ('5.5.5.5', 10000),
    ('6.6.6.6', 31000),
    ('7.7.7.7', 100),
    ('8.8.8.8', 300),
    ('9.9.9.9', 10000)
GO

This table holds an aggegated value of the number of requests a given ClientIp has made on a system. Now you want to identify the top three ClientIps that have generated the most requests. That easy, right? Without thinking too much about it, I would have written something like this:

SELECT TOP (3) *
FROM AggregatedClientRequests
ORDER BY NumOfRequests DESC

That looks right… or does it? What about the ClientIps ‘5.5.5.5’ and ‘9.9.9.9’ ? They also generated 10.000 requests, so they are actually equally to the ‘2.2.2.2’ ip. So which one of the three with 10.000 request should I return?. If I want those returned as well (think of a use case where the top three are sales persons that should have a bonus), how could I do that? That’s actually pretty simple by using the TOP WITH TIES clause like this:

SELECT TOP (3) WITH TIES *
FROM AggregatedClientRequests
ORDER BY NumOfRequests DESC

Now it get this back:

Notice that I actually get 5 rows returned, even though my TOP clause stated that I only wanted three! The WITH TIES clause looks at the last row of the TOP (3) rows, and add all other rows with identical values.

How simple is that?! I don’t even want to think about how the query would look to achieve the same thing without using the WITH TIES clause. Perhaps you will give it a go?

@geniiiuscom