drop table tt_test;

create table tt_test ( id number primary key, s1 varchar2(10), s2 varchar2(10) );
insert into tt_test values ( 1, 's1', 's1_1' );
insert into tt_test values ( 2, 's1', 's1_2' );
insert into tt_test values ( 3, 's2', 's2_1' );
insert into tt_test values ( 4, 's1', 's1_3' );
insert into tt_test values ( 5, 's2', 's2_2' );
insert into tt_test values ( 6, 's3', 's3_1' );
insert into tt_test values ( 7, 's1', 's1_4' );

-- нормальный способ с помощью аналитики
select row_number() over (order by s1) r, t1.*
from tt_test t1
order by r;

-- по уникальному полю
-- (на oracle: sort merge join)
select count(1) as rank, t1.id, t1.s1, t1.s2 from tt_test t1, tt_test t2
where t1.id >= t2.id 
group by t1.id, t1.s1, t1.s2
order by rank;

-- по не уникальному полю, в принципе результат логичный
-- и даже осмысленный (ранк группы)
-- (на oracle: sort merge join)
select count(1) as rank, t1.id, t1.s1, t1.s2 from tt_test t1, tt_test t2
where t1.s1 >= t2.s1 
group by t1.id, t1.s1, t1.s2
order by rank, t1.id;

-- по двум полям (уникальная связка)
-- база плакала, индексы накрывались медным тазом
-- (на oracle: nested loops по full table scan)
select count(1) as rank, t1.id, t1.s1, t1.s2 from tt_test t1, tt_test t2
where t1.s1 > t2.s1  or ( t1.s1=t2.s1 and t1.id >= t2.id )
group by t1.id, t1.s1, t1.s2
order by rank;

-- Проктологическая пагинация
-- Страница 2 (по 3 записи на страницу)
WITH Results_CTE AS (
select count(1) as rank, t1.id, t1.s1, t1.s2 from tt_test t1, tt_test t2
where t1.s1 > t2.s1  or ( t1.s1=t2.s1 and t1.id >= t2.id )
group by t1.id, t1.s1, t1.s2
)
SELECT * FROM Results_CTE
WHERE rank >= 3 and rank < 6