Re: SQL Query Help

Wallace Fukumae wrote:
>
> Problem:
> A tableT has two fields 'fieldA', 'fieldB'. fieldA is a character field
> but contains
> only numbers for priorities. fieldB is a character field identifying the
> record.
> I would like to generate a query which would return rownum,fieldA,fieldB
> from
> tableT but ordered by fieldA. For example, I would like to return the top
> 500 records from tableT. However, as we know if you use the order by
> clause with rownum, rownum is based only off of the select, so rownum
> would be correct for the select but as soon as the sorting takes place
> with the order by clause rownum would be out of sync.
>
> TABLE T:
> fieldA number
> fieldB varchar2(10)
>
> SAMPLE DATA:
> HONDA 44
> TOYOTA 45
> HYUNDAI 90
> FORD 5
> CHEVORLET 8
>
> DESIRE:
> 1 FORD 5
> 2 CHEVORLET 8
> 3 HONDA 44
> 4 TOYOTA 45
> 5 HYUNDAI 90
>
> Thanks, Wally

If you're using 8.1.6 or higher this will work:

select rownum, a.*
from (select fielda, fieldb from test order by fielda) a ;

Versions of Oracle below 8.1 don't seem to like the 'order by' clause in the subquery. I've been playing around with using a sequence, but they don't like order by's either. Received on Wed Jun 13 2001 - 12:13:36 CDT