TOP WITH TIES clause in SELECT queries

Introduction

The SELECT TOP N query always returns exactly N records, and randomly drops any record that have the same value as the last record in the group.

SELECT TOP 5 price, Booktitle FROM BookTitles ORDER BY price DESC

This query will give 5 records from table BookTitles in descending order by price. Problem over here is Suppose the last book title has a price tag of $19.99. The BookTitles table contains two more books with the same price, but they are ignored by the TOP clause.

To see those recrods add the WITH TIES clause…

SELECT TOP 5 WITH TIES price, Booktitle FROM BookTitles ORDER BY price DESC

**WITH TIES will only work with Order by Clause.

RSS Feed