select
(top 1 id from table t where (select count(*) from table t2 where t2.mainid=t.mainid and t2.id<=t.id)<=@index order by id desc) as id,
mainid,
select (top 1 name from table t where (select count(*) from table t2 where t2.mainid=t.mainid and t2.id<=t.id)<=@index order by id desc) as name
from table
group by mainid
create table test(id int identity(1,1),MainID int,name char(1))
insert test values(1,'a')
insert test values(1,'b')
insert test values(1,'c')
insert test values(2,'d')
insert test values(2,'e')
insert test values(3,'f')
create proc t2 @index int
as
select a.* from test a,(select case When min(id)+@index-1 > max(id) then max(id) else min(id)+@index-1 end id from test group by mainid) b
where a.id=b.id
create table test(id int identity(1,1),MainID int,name char(1))
insert test values(1,'a')
insert test values(1,'b')
insert test values(1,'c')
insert test values(2,'d')
insert test values(2,'e')
insert test values(3,'f')
create procedure t @index int
As
declare @s varchar(1000)
set @s = 'Select * from test a where id = (select top 1 id from (select top ' + cast(@index as varchar(1)) + ' id from test where Mainid =a.MainID order by id ) aa order by id desc) '
exec(@s)