Re: 'Order by' in a subquery

"Theo" <theoa_nl_at_hotmail.com> wrote in message news:<40423769$0$8926$e4fe514c_at_dreader9.news.xs4all.nl>...
> "bob" <bobo_at_yahoo.fr> wrote in message
> news:c1n5o7$tf3$1_at_s1.read.news.oleane.net...
> >
> > I have a problem with a query which looks like below
> >
> > SELECT r_num, T.Field1, T.Fiel2
> > FROM (
> > SELECT rownum as r_num,
> > Field1,Field2
> > FROM table1
> > order by field1
> > ) T
> > where r_num between 1 and 5
> >
> > I have a problem with the 'Order by' clause, but I need to sort the
> records
> > in the subquery.
>
> What exactly is your problem? This query should work, although it's a bit
> more complicated than necessary:
>
> select rownum, field1, field2
> from (
> select field1, field2
> from table1
> order by field 1
> )
> where rownum <= 5
>
> Theo

Actually your query will work, the original one won't. rownum is calcualted BEFORE order by.

Another way of doing it is using analytic function row_number(). Received on Fri Mar 05 2004 - 19:23:25 CET