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.
