Re: ROWNUM question??

Oscar Paredes wrote in message <36A4E33D.74474199_at_fib.upc.es>...
>
>Hi all!
>
>I want to select the first row of a SELECT statement
>that have a ORDER BY, like:
>
> select e.name
> from employee e
> where rownum=1
> order by e.name
>
>But,... unfortunatelly... this don't work!! :-(
>
>Someone know how to do this????
>
>
>Thanks,
>Oscar
>

Simply saying where rownum = 1 or rownum < 2 will not work. The problem is the use of rownum with an order by. Oracle applies the rownum before the order by ( as applying the order by first would cause the whole result set to built). This means that (with the clause rownum = 1) the first row that is found to match the criteria (rownum = 1) will be returned. Since oracle queries are not guaranteed order unless an index is used, you will most likely get the first record inserted into this table.

To get your query to return the first e.name, you need to have an index with name as the first column and either supply a name as part of the where clause or us an index hint.

index hint syntax:
/*+ index(table_name index_name) */

example using table e, index eindex01:
select /*+ index(e eindex01) */e.name
from employee e
where rownum=1
order by e.name

This will allow you to get the name in the correct order from the table e.

Hope this helps,

dsleuth Received on Tue Jan 19 1999 - 20:21:28 CST