Re: "limit 10" vs "rownum <=10"

Mark Harrison <mh_at_pixar.com> wrote:
> So, we've got some inter-database portability issues we're trying
> to address.
>
> sqlite uses: select * from foo limit 1;
> oracle uses: select * from foo where rownum <= 1;
>
> What's the conventional wisdom on how to handle this?
> Is there something in oracle we can turn on to help?
>
> Right now we're thinking of some logic like:
> if oracle, string substitute "limit x" with "where rownum <= x"
> and try to handle the cases when there is already a where clause, etc.

What does 'limit 1' do in sqlite?

If it is similar to PostgreSQL, there is a big difference. You wouldn't see it in your example though, but then randomly selecting any row from a table is not a frequent request.

In Oracle, if you

SELECT col1, col2 FROM tab WHERE ROWNUM <= 1 ORDER BY col1;

you will NOT get the row with the smallest col1.

Yours,
Laurenz Albe Received on Tue Aug 01 2006 - 14:23:57 CDT