public knowledge blog_load(sender Virendra)

                                                                                                                          Nothing shocks me, I am a Software Engineer.

TOP WITH TIES clause in SELECT queries

Posted by Virendra Dugar on January 12, 2009

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: