select
a.*
from
Suburl a
where
SubUrl in(select top 2 SubUrl from SubUrl where MainID=a.MainID order by SubUrl desc)
order by
a.SubUrl,a.MainID
a.*
from
Suburl a
where
SubUrl in(select top 2 SubUrl from SubUrl where MainID=a.MainID order by SubUrl desc)
order by
a.SubUrl,a.MainID
insert into @t select 1,'1111111111'
insert into @t select 1,'2222222222'
insert into @t select 1,'3333333333'
insert into @t select 1,'4444444444'
insert into @t select 2,'1111111111'
insert into @t select 2,'2222222222'
insert into @t select 2,'3333333333'
insert into @t select 2,'4444444444' select
a.*
from
@t a
where
a.SubUrl in(select top 2 SubUrl from @t where MainID=a.MainID order by SubUrl desc)
order by
a.MainID,a.Suburl/*
MainID SubUrl
----------- --------------------
1 3333333333
1 4444444444
2 3333333333
2 4444444444
*/
insert into @t select 1,'1111111111'
insert into @t select 1,'2222222222'
insert into @t select 1,'3333333333'
insert into @t select 1,'4444444444'
insert into @t select 2,'1111111111'
insert into @t select 2,'2222222222'
insert into @t select 2,'3333333333'
insert into @t select 2,'4444444444'
select * from @t a where(select count(1) from @t where mainid=a.mainid and suburl>=a.suburl)<=2
select MainID,SubUrl = (select top 2 SubUrl from Suburl where MainID = t1.MainID order by SubUrl desc)
from Suburl t1
select MainID,SubUrl
from @t t1
where suburl in
(select top 2 SubUrl from @t where MainID = t1.MainID order by SubUrl desc)