What to do with TOP when there are ties

In the last few blog posts – see here and here – we’ve been discussing the usage of the TOP operator. The product team will often ask for the top 5 of something or even the top 10% of performers in a subregion.

There’s an additional part to the TOP operator that you won’t see very often but it is very helpful when you need it. That part allows you to show those records that are tied to be in the TOP grouping that you’ve requested.

To do this correctly, we need some data that has some great numbers to work with. For that, we turn to our friends at data.world for a list of all of the live Nirvana performances found here.

Please feel free to import the data into SQL Server your favorite way. If you don’t have a favorite way yet, please feel free to review my past blog posts on the matter of importing data here.

Here is the table definition that I used so that you can follow along with my queries:

CREATE TABLE dbo.NirvanaPerformances
(	ConcertDate VARCHAR(10) NOT NULL,
	Venue VARCHAR(75) NOT NULL,
	City VARCHAR(20) NOT NULL,
	State CHAR(2) NULL,
	Country VARCHAR(20) NOT NULL,
	RecordingAvail CHAR(1) NOT NULL)

Originally, we would have used the following query to get the top 10 performances from Germany.

SELECT TOP (10) * 
FROM dbo.nirvanaPerformances
WHERE COuntry = 'Germany'
ORDER BY ConcertDate DESC

Now – and here’s the magic – if we want to grab every concert they did – especially the last 2 in Munich, then you’ll have to use our new addition – WITH TIES.

I am sure that you can think of many different places where WITH TIES can be helpful in your reporting or analyst duties.

And so, with that, I bid you adieu. And until next time!