Wednesday, January 28, 2009

Use of TOP WITH TIES clause in SELECT queries

This caluse can be used to get additional rows from the base result set with the same value .
That is if the last of returned result set consists the same vlaue of order by item for other rows, those rows will also be returned.
- This can be only used in select statemnet.
- Order by clause is necessary for using this clause
- This can also be used with PERCENT clause.

Example:-

if object_id('test_top','U') is not NULL
drop table test_top
GO
create table test_top (a int, b int)
GO
insert into test_top
select 1,1
union all
select 2,1
union all
select 3,3
union all
select 4,3
go

Now,
select top 1 b from test_top

This will return only one row.

/*
b
----
1
*/



so,
select top 1 with ties b from test_top order by b

--this will return 2 rows as

/*
b
---
1
1
*/


But when we use top 100 percent caluse "with ties" not needed.