Re: Problem with rownum

Norbert Biermayr wrote:

> The following query shows a right result:
>
> "Select Nummer From Artikel where Nummer > 60000 and Rownum < 2 Order by Nummer"
>
> The result is the correct number 60001.
>
> With the statement
>
> "Select * From Artikel where Nummer > 60000 and Rownum < 2 Order by Nummer"
>
> I get a wrong result: Number 68048.
>
> Has anyone an explanation for this behavior?

Yes, the where clause is executed before the order by clause. In particular, you're choosing the first row of a random ordering, rather than ordering by nummer and then choosing the first one.

Try this:

Select * from
(Select * From Artikel where Nummer > 60000 Order by Nummer) where Rownum < 2

Note that this isn't necessarily the best way to get your result, but it probably is what you are *trying* to do with your query. Observe the difference.

Using rownum in a where clause can often produce undefined results - not "wrong" results, but undefined results. Both results you report above are correct, it's just that your query doesn't uniquely specify the result set. My advice is to avoid using rownum in a where clause until you really understand what it is and what it isn't.

-- 
//-Walt
//
//