Understanding the TOP WITH TIES clause in SELECT queries [ All Languages » SQL Server »  T-SQL] Total Hit ( 3152) Rate this article:     Poor     Excellent   Submit Your Question/Comment about this article Rating

The SELECT TOP N query always return exactly N records, and arbitrarily drops any record that have the same value as the last record in the group. To see what this means in practice, execute the following query against the Pubs database in SQL Server 7.0: Click here to copy the following block SELECT TOP 5 price, title FROM titles ORDER BY price DESC The last title (it should be "The Busy Executive's Database Guide") has a price tag of $19.99. The Titles table, however, contains two more books with the same price, but they are ignored by the TOP clause. To see them you must add the WITH TIES clause, as in: Click here to copy the following block SELECT TOP 5 WITH TIES price, title FROM titles ORDER BY price DESC The same rule applies to the TOP N PERCENT clause. Try executing the following query with and without the WITH TIES clause: Click here to copy the following block SELECT TOP 25 PERCENT WITH TIES price, title FROM titles ORDER BY price DESC

Related Article(s)

Submitted By : Nayan Patel  (Member Since : 5/26/2004 12:23:06 PM)
	Job Description : He is the moderator of this site and currently working as an independent consultant. He works with VB.net/ASP.net, SQL Server and other MS technologies. He is MCSD.net, MCDBA and MCSE. In his free time he likes to watch funny movies and doing oil painting.
View all (893) submissions by this author  (Birth Date : 7/14/1981 )