Sequence number based on Group values

create table #tmp12
(
    isqno int ,
    crow1 VARCHAR(10),
    crow2 VARCHAR(10)
    )


insert into #tmp12 values(1,'A','Z')
insert into #tmp12 values(2,'A','Y')
insert into #tmp12 values(3,'B','X')
insert into #tmp12 values(4,'B','W')


select isqno,crow1,DENSE_RANK() over(order by crow1  ) Group_order,
crow2
from #tmp12


isqno    crow1    Group_order    crow2
1         A              1                    Z
2        A               1                   Y
3        B               2                    X
4        B               2                   W

To fetch the Nth row in the table

SELECT * FROM (    SELECT *, ROW_NUMBER() OVER (ORDER BY (SELECT '')) AS  RowIndex FROM #tmp12 ) T1
WHERE T1.RowIndex = (select count(*) from #tmp12)