Mar 042013
 
select a_random_row
from   this_table
where  rownum = 1
and    i_wanted_the_first = 'Y'
order  by according_to_this;
This issue is slightly more insidious than last week's, though based on a similar misunderstanding. 
This query can appear to be workingly correctly, until the physical ordering of data changes 
(e.g. when migrating to a new environment); at which point it becomes apparent that the order by is done after rownum

For the correct way to do top-n analysis in Oracle, see this.

If you enjoyed this article, Get email updates (It’s Free)

 Leave a Reply

(required)

(required)

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>