Not many people seem to be aware of the WITH TIES clause introduced in SQL Server 2005+. This simple feature is worth adding to your query armoury. In the words of BOL WITH TIES …

“Specifies that additional rows be returned from the base result set with the same value in the ORDER BY columns appearing as the last of the TOP n (PERCENT) rows. TOP…WITH TIES can be specified only in SELECT statements, and only if an ORDER BY clause is specified.”

Many of us would have been asked in our professional lives “Give me the top n of <whatever>“ and would have automatically thought of using TOP. Lets take the following scenario…

CREATE TABLE SalesPerson
(
	Id INT NOT NULL PRIMARY KEY CLUSTERED IDENTITY(1, 1),
	FirstName VARCHAR(30) NOT NULL,
	LastName VARCHAR(30) NOT NULL,
	CurrentMonthSales MONEY NOT NULL DEFAULT '0.00',
);

Now insert some test data…

INSERT INTO SalesPerson
(
	FirstName,
	LastName,
	CurrentMonthSales
)
VALUES
(
	'Rhys',
	'Campbell',
	'1000.00'
);
INSERT INTO SalesPerson
(
	FirstName,
	LastName,
	CurrentMonthSales
)
VALUES
(
	'John',
	'Doe',
	'3500.00'
);
INSERT INTO SalesPerson
(
	FirstName,
	LastName,
	CurrentMonthSales
)
VALUES
(
	'Joe',
	'Bloggs',
	'3500.00'
);
INSERT INTO SalesPerson
(
	FirstName,
	LastName,
	CurrentMonthSales
)
VALUES
(
	'Jane',
	'Doe',
	'3500.00'
);
INSERT INTO SalesPerson
(
	FirstName,
	LastName,
	CurrentMonthSales
)
VALUES
(
	'John',
	'Smith',
	'5000.00'
);

Now if the under pressure DBA was asked for the top 3 performing sales people this month they might blast out something like…

SELECT TOP 3 *
FROM SalesPerson
ORDER BY CurrentMonthSales DESC;

image

In many circumstances this may not be an issue. But here it could cause one of our friends in sales to miss out on a bonus! So keep friendly with sales and use the WITH TIES   clause when appropriate.

SELECT TOP 3 WITH TIES *
FROM SalesPerson
ORDER BY CurrentMonthSales DESC;

image