devxlogo

Understanding the TOP WITH TIES clause in SELECT queries

Understanding the TOP WITH TIES clause in SELECT queries

Why Trust Us

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:

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:

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:

SELECT TOP 25 PERCENT WITH TIES price, title FROM titles ORDER BY price DESC
See also  Professionalism Starts in Your Inbox: Keys to Presenting Your Best Self in Email
devxblackblue

About Our Editorial Process

At DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere.

See our full editorial policy.

About Our Journalist

Charlie Frank

Charlie has over a decade of experience in website administration and technology management. As the site admin, he oversees all technical aspects of running a high-traffic online platform, ensuring optimal performance, security, and user experience.
View Author
Show More