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

Mark Harrison 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.
>
> Many TIA,
> Mark
>
> --
> Mark Harrison
> Pixar Animation Studios

LIMIT is not in tthe SQL standard, so it is a vendor specific solution.

ROWNUM is a pseudoColumn in ORACLE, and so does not violate the standard (at least not directly)

The proper way of handling either case is in the application code that fetches the results. The app opens a cursor and fetches until it reaches its limit. If you do the application right, then you won't need either ROWNUM or LIMIT.

HTH,
   ed Received on Tue Aug 01 2006 - 14:27:53 CDT