Thursday, January 21, 2016

SQL Server Transact SQL - TSQL - TOP WITH TIES

TOP Keyword is commonly used to get the Top Rows from a Result. SQL Server has option use it 'WITH TIES' to get similar rows together

WITH TIES
Used when you want to return two or more rows that tie for last place in the limited results set. Must be used with the ORDER BY clause. WITH TIES may cause more rows to be returned than the value specified in expression. For example, if expression is set to 5 but 2 additional rows match the values of the ORDER BY columns in row 5, the result set will contain 7 rows.

SELECT TOP (5) productid, unitprice FROM Production.Products
WHERE categoryid = 1 ORDER BY unitprice DESC;

You get the following result set.


productid                unitprice
----------- ---------------------
38                           263.50
43                             46.00
2                               19.00
1                               18.00
35                             18.00

If you want provide solutions to turn this query into a deterministic one—one solution that includes ties and another that breaks the ties. First, address the version that includes all ties by using the WITH TIES option. Add this option to the query, as follows.

SELECT TOP (5) WITH TIES productid, unitprice FROM Production.Products
WHERE categoryid = 1 ORDER BY unitprice DESC;

You get the following output, which includes ties.
productid                unitprice
----------- ---------------------
38                           263.50
43                             46.00
2                               19.00
1                               18.00
35                             18.00
76                             18.00

2 comments:

  1. Thanks for sharing this Informative content. Well explained. Got to learn new things from your Blog on. SQL server dba Online Training

    ReplyDelete
  2. free parking software
    apartments
    violation management
    Software for parking enforcement, parking violations and parking management”
    World-wide-web solutions in a group retain the most important data fiscal information and other very sensitive and exclusive information will be transported and located. Property or home Operators condo supervisors, Panel Associates, professional property or home and linked to overseeing was able car parking setting.
    You need dependable, progressive in addition to successful software program to handle your current businesses auto parking in addition to customer passes. You desire a built-in process that enables to be able to easily tracking check in addition to alter exactly where persons park your car in addition to have. Entrance functions achieve all this together with effective however convenient to use process agreed upon to guarantee the safety measures of your campus in addition to lots. A era your current campus lots auto parking structure sub-sections together with custom made place assignments, Car or truck Registration together with in decal allocation in addition to rebirth procedure, Auto parking Citations in addition to accounts receivable functions.

    ReplyDelete