WITH TIES

[VBXL]

In an sql statement, what does the WITH TIES clause do???


VBXL

 

[arrowhouse]

This is used when you are using the TOP option to specify that only the top n (percent) of a result set be returned - it means that rows with the same values (see below) will be included in the result set - even it that takes you over the number/percentage of rows specified.

FROM BOL:

Specifies that additional rows be returned from the base result set with the same value in the ORDER BY columns appearing as the last of the TOP n (PERCENT) rows. TOP ...WITH TIES can only be specified if an ORDER BY clause is specified.

 

[VBXL]

Hi,

In this example, what would be the point of using the TOP statement ???

 

[tlbroadbent]

Assume your table has 1000 rows. You specify Top 10 With Ties ordered descending by a quantity field. If the Top 10 quantities are unique, you'll get 10 values. However, if the 10th value has duplicates, you'll also get all the duplicates regardless of the number of duplicates. That may result in 11 or more rows.

Usually the result set will contain fewer than 1000 rows from the table. However, if the table contains only 10 unique values, all 1000 rows will be returned when With Ties is specified. If you don't specify With Ties then only 10 rows will be returned regardless of the number of unique values or duplicates.

I hope this clarifies the point of using the TOP statement. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[VBXL]

Cheers