retrieving specified rows

Does anyone have an elegant way (sql) to query:

select (various columns) from (several joined tables) where
(resultset_row_numbers is between specified_first_row and
specified_last_row)

This would be really handy

Thanks,
kim