Thursday, July 22, 2010

What is WITH TIES clause in SQL Server?

Have you ever used WITH TIES keyword?
Actually TOP keyword specifies that only the first set of rows will be returned from the query result. The set of rows can be either a number or a percent of the rows. Here is Transact-SQL Syntax:
[
      TOP (expression) [PERCENT] 
      [ WITH TIES ]
 ]

Arguments
expression: is the numeric expression that specifies the number of rows to be returned. expression is implicitly converted to a float value if PERCENT is specified; otherwise, it is converted to bigint.

If the query includes an ORDER BY clause, the first expression rows, or expression percent of rows, ordered by the ORDER BY clause are returned. If the query has no ORDER BY clause, the order of the rows is arbitrary.

PERCENT: indicates that the query returns only the first expression percent of rows from the result set.

WITH TIES: 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 be specified only in SELECT statements, and only if an ORDER BY clause is specified.

Example:

DECLARE @TEST TABLE(
ID int, Amount int)
INSERT INTO @TEST VALUES
(1,10),(2,30),(3,60),
(4,50),(5,50),(6,70),
(7,20),(8,70),(9,70),
(10,44),(11,80),(12,90)

Below is the result-set using TOP without and with clause WITH TIES.


No comments:

Post a Comment