Re: Oracle Queries/Paging

mlecza_at_my-deja.com wrote:
>
> Thanx for the reply
>
> Interesting suggestion but we would need the flexibility to order the
> nested query (if only rownum's were assigned after the ORDER BY)
>
> > select rn, col_1, col_2
> > (select rownum rn, col_1, col_2
> > from YOUR_TABLE )
> > where rn between :START_ROW and :END_ROW
> > order by col_1
> >
> > Pay attention to that u cannot use ORDER BY clause in nested select.
U can build index for columns that u wanna to sort. And use hints.. [Quoted] Or use next nested select:

For ex we need to sort by Col_1, Col_2
build index:
create index YOUR_INDEX on YOUR_TABLE (Col_1, Col_2)

use in select group by clause that implicitly use YOUR_IDEX and sort data

select rn, col_1, col_2, Col_3, Col_4
(select rownum rn, col_1, col_2, min(Col_3), Min(Col4)  from YOUR_TABLE )
 where rn between :START_ROW and :END_ROW Received on Fri Oct 15 1999 - 12:29:16 CEST