Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: SS7,VB6,VBS
Expertise: beginner
Apr 29, 2000

Understanding the TOP WITH TIES clause in SELECT queries

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
Francesco Balena
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap