Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> 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..
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 - 05:29:16 CDT