SQL Server: TOP WITH TIES

SQL Server: TOP WITH TIES

TOP clause is commonly used to get top required rows from a result set. Beauty of this clause is that it can be used with WITH TIES clause, to retrieve all similar rows to base result set.

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.