Understanding WITH TIES Option in SQL Server

Table Structure:
=-=-=-=-=-=-=-=-
Book_ID Book_Name Book_Price
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
B001 ABC $10.50
B002 AAA $12.50
B003 BBC $10.50
B004 KCC $16.50
B005 MCC $10.50
B006 DCC $17.50
B007 BBA $13.50
B008 MMC $10.50
B009 BOOK $07.10
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

if you use TOP clause with the SELECT statement it will only gives you the N rows even if there is any record of same value available.

Consider this example:

select top 5 Book_Name,Book_Price from Book order by Book_Price desc

Resule of this query is will be:

Book_Name Book_Price
=-=-=-=-=-=-=-=-=-=-=-=-=-
DCC $17.50
KCC $16.50
BBA $13.50
AAA $12.50
MCC $10.50
=================
But there is also some books with the same price ($10.50). So those records will be ignored by TOP Clause.

But If we think as book seller's mind they also wanted to list the name of those books which comes in that top 5 price range because they have to sell those boks also because those are also coming in TOP 5 price range.

So, to solve this issue we will use WITH TIES Clause.

Consider this Example:

select top 5 with ties Book_Name,Book_Price from Book order by Book_Price desc

Result of this query will be:

Book_Name Book_Price
=-=-=-=-=-=-=-=-=-=-=-=-=-
DCC $17.50
KCC $16.50
BBA $13.50
AAA $12.50
BBC $10.50
MCC $10.50
MMC $10.50
ABC $10.50
=======================

Now we are getting the expected output; here we can see all the book name of top 5 price range.

===============================
Enjoy...