Sunday, March 7, 2010

Top 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.

The following example obtains the top 10 percent of all employees with the highest salary
and returns them in descending order according to salary base rate.
Specifying WITH TIES makes sure that any employees that have salaries equal to the
lowest salary returned are also included in the result set,
even if doing this exceeds 10 percent of employees.

Here We Go..!!!

DECLARE @A TABLE (ID INT IDENTITY,NAM VARCHAR(12),SALARY INT)
INSERT INTO @A VALUES('A',1000)
INSERT INTO @A VALUES('B',5000)
INSERT INTO @A VALUES('C',3000)
INSERT INTO @A VALUES('D',1000)
INSERT INTO @A VALUES('E',2000)
INSERT INTO @A VALUES('F',1000)
SELECT * FROM @A ORDER BY SALARY



TOP(n)PERCENT WITH TIES :
SELECT TOP(10) PERCENT WITH TIES * FROM @A ORDER BY SALARY



TOP(n)WITH TIES :
SELECT TOP(1) WITH TIES * FROM @A ORDER BY SALARY




No comments:

Post a Comment