/* ------------------------ My Meta Content Here SEO ------------------------ */

Pages

Main Menu




Wednesday, July 24, 2013

SQL SERVER - WITH TIES clause in sql server


What is WITH TIES clause in SQL Server?

A Beauty of TSQL.

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)

select Top 3 ID, Amount from @TEST ORDER BY Amount desc

select Top 3 With TIES ID, Amount from @TEST ORDER BY Amount desc

select * from @TEST

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

If you will not uses the ORDER BY Clause then it will give the following errors:
Msg 1062, Level 15, State 1, Line 12
The TOP N WITH TIES clause is not allowed without a corresponding ORDER BY clause.

No comments:

Post a Comment

Recent Posts

My Blog List