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)