HOME

Tuesday, February 14, 2012

usage of TIES WITH Clause in SQL SERVER


If we are using SELECT TOP N query, then it returns exactly N records, and drops any record arbitrarily that has the same value as the last record in the result set.
Suppose we want a report showing top 50 costly items. There may be a situation in which the price of 50th item is same as one or more items down the list. At that point how can we have a better solution?  Do we want to include other products that are tied for the same prices as the item in the 50th position?
There is an easy way to solve the problem caused by tied values in the last position of your top list arbitrarily capping the results. And it the SQL clause "With TIES"
SELECT TOP 50 WITH TIES NAME, PRICE
FROM PRODUCT
ORDER BY PRICE DESC
The result set  would have more than 50 items, but the last 2 or more items would be having the same price.

No comments:

Post a Comment