Oracle:
select *
from ( select a.*, rownum rnum
from ( YOUR_QUERY_GOES_HERE -- including the order by ) a
where rownum <= MAX_ROWS )
where rnum >= MIN_ROWS
/
SQL Server:
select * from
(
select row_number() over(order by rownum) as row_num, * from
(
select col1,col2,col3,row_number() over (order by col4 desc) as rownum
from table_1
) as dt1 where dt1.rownum<50) as dt2
where dt2.row_num>=25;
DB2:
select * from
(select rownumber() over() as row_num, inner_table.* from
(select * from user_master order by user_name desc fetch first 50 rows only) as inner_table)
) as outer_table where outer_table.row_num>25
optimize for 50 rows;
Conclusion:
1. use analytical function or keyword
2. embeded subquery