TOP WITH TIES clause in SELECT queries

Virendradugar
Posted by in Sql Server category on for Beginner level | Views : 3654 red flag
Rating: 4 out of 5  
 1 vote(s)

This article explains the TOP with TIES clause.
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.



Enjoy...

Page copy protected against web site content infringement by Copyscape

About the Author

Virendradugar
Full Name: Virendra Dugar
Member Level: Silver
Member Status: Member,MVP
Member Since: 8/11/2009 4:14:05 AM
Country: India

http://jquerybyexample.blogspot.com
Virendra Dugar is experienced Senior Software Developer with over 5 years of hands-on experience working with Microsoft .NET technology (ASP.NET, C#, VB.NET,SQL Server). He is always keen to learn new technology. He holds a Master's Degree in Computer Application & Information technology from Gujarat University in india.In free time, he loves to listen music, read books, play games and do blogging etc. Visit his blogs : http://jquerybyexample.blogspot.com

Login to vote for this post.

Comments or Responses

Posted by: S.faizaan on: 7/11/2011 | Points: 25
thanks for sharing this...!!!
regards

Login to post response

Comment using Facebook(Author doesn't get notification)