SELECT TOP … WITH TIES

Ever wanted to query a table and get all records that have one of the top 3 values?
Let’s say you have a Books table where all your book details are stored.

You want to find the top 3 books that has the same price value, but you want to include “ties”. Let me show you with code.

DECLARE @Books TABLE (
Title NVARCHAR(200),
Price FLOAT
)

INSERT INTO @Books
(Title, Price)
VALUES
(‘SQL’, 1500),
(‘C#’, 1250),
(‘VB .Net’, 1250),
(‘ASP .Net’, 1250),

SELECT TOP (3) WITH TIES
*
FROM @Books
ORDER BY Price DESC

The results will be 4 rows:

Title Price
SQL 1500
C# 1250
VB .Net 1250
ASP .Net 1250

Pretty neat?