Re: Ordering select results without ORDER BY

There are so much ways to do it....
-- easiest way:

select * from
  (select * from checks
    where check_status = 'A'
     order by check_no
  )
where rownum = 1

• fastest way, but requires index on check_no field

create index i_checks_check_no on checks(check_no);

select /*+ index(checks index i_checks_check_no)*/   *
  from checks
 where check_no>-9.99e99

     and check_status='A'
     and rownum=1;

• Most correct way, if you have index by check_no field

select * from checks
where rownum=1

   and check_status='A'
  and CHECK_NO = (select min(CHECK_NO)

                                   from checks
                                   where status='A');

"Ted O'Connor" <google_at_toconnor.com> wrote in message news:4b29c484.0112110623.16b40104_at_posting.google.com...
> I have some vendor code that I am supporting that has some incorrect
> SQL. Currently it is trying to get the lowest available check number
> from a checks table using the following SQL:
>
> select * from checks
> where rownum = 1 and check_status = 'A'
> order by check_no
>
> I know this returns the wrong number since the rownum is being applied
> before the order by. I have fixed the SQL but cannot apply the code
> change for two weeks because of a server migration. In the meantime
> is there any way I can order the checks table physically by check_no
> (not currently a key or index) so the above SQL will return the
> correct result?
>
> Thanks.
Received on Tue Dec 11 2001 - 14:10:50 CST