Who supports the LIMIT clause in a SELECT statement
Someone asked whether databases other than MySQL support a LIMIT clause to fetch a specific range of rows (e.g. rows 30-49) from a SELECT. Replies: Oracle can approximate it via the ROWNUM pseudo-column, with the caveat that ROWNUM doesn't match the order when ORDER BY is used; Informix (from 7.30) offers SELECT FIRST n, but with no SQL way to skip leading rows; PostgreSQL 6.4.2+ supports MySQL-style LIMIT. Suggested workarounds for Informix/Sybase were stored procedures or dynamically built SQL, though stored procedures were seen as too rigid. No single clean equivalent was found.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
MySQL has this cool feature where you can specify how many (and
which) rows are returned from a SELECT statement. I was wondering
if any other, more full featured, databases (e.g. Oracle, Sybase,
Informix, etc.) have this feature.
You would use it something like this:
SELECT * FROM bar LIMIT 30, 20
In order retreive rows 30 through 49 of the table returned by the
SELECT.
Cheers,
Rob
Robert S. Mah <rmah@arena-i.com> wrote in article
<rmah-0902991740420001@p20.ts1.angel.net>...
> MySQL has this cool feature where you can specify how many (and
> which) rows are returned from a SELECT statement. I was wondering
> if any other, more full featured, databases (e.g. Oracle, Sybase,
> Informix, etc.) have this feature.
>
> You would use it something like this:
>
> SELECT * FROM bar LIMIT 30, 20>
> In order retreive rows 30 through 49 of the table returned by the
> SELECT.
>
Hi.
You can do this in Oracle by looking at ROWNUM, a pseudo-column that each
row has. Ie the first row returned as a ROWNUM of 1, the second 2 etc.
etc.
BUT, if you do any kind of sorting in the query, eg an 'ORDER BY' by
clause, ROWNUM may not correspond to the order in which the rows are
returned. Eg. ROWNUM 1 may not be the first row in your results etc.
So you can do it, but you have to be careful. :-)
-Richard
"Robert S. Mah" wrote:
>
> MySQL has this cool feature where you can specify how many (and
> which) rows are returned from a SELECT statement. I was wondering
> if any other, more full featured, databases (e.g. Oracle, Sybase,
> Informix, etc.) have this feature.
>
> You would use it something like this:
>
> SELECT * FROM bar LIMIT 30, 20>
> In order retreive rows 30 through 49 of the table returned by the
> SELECT.
>
> Cheers,
> Rob
Hi Robert,
in Informix (since 7.30 ) it is:
SELECT FIRST 49 * FROM BAR
No possibility to skip the first line by SQL,at least none that i am aware of.
hth
martin
Postgresql http://www.postgresql.ru since 6.4.2 + feature patch also supports the same syntax as MySQL does for SELECT with LIMIT Regards, Oleg -- _____________________________________________________________ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83
On Tue, 9 Feb 1999 22:40:42 GMT, rmah@arena-i.com (Robert S. Mah)
wrote:
>MySQL has this cool feature where you can specify how many (and
>which) rows are returned from a SELECT statement. I was wondering
>if any other, more full featured, databases (e.g. Oracle, Sybase,
>Informix, etc.) have this feature.
>
>You would use it something like this:
>
> SELECT * FROM bar LIMIT 30, 20>
>In order retreive rows 30 through 49 of the table returned by the
>SELECT.
Oh, hell, nobody else has suggested it.....
In Informix, write a stored procedure. Not perfect and might not
answer all (most??) requirements (I'm thinking of a generic editor,
for example), I can't remember if you can PREPARE stuff in SPL, but
given a heap of caveats, it's possible to do some stuff like this.
Peter Wiley
> Oh, hell, nobody else has suggested it..... > > In Informix, write a stored procedure. Not perfect and might not > answer all (most??) requirements (I'm thinking of a generic editor, > for example), I can't remember if you can PREPARE stuff in SPL, but > given a heap of caveats, it's possible to do some stuff like this. > > Peter Wiley My experience has shown that stored procedures are just to rigid since they can't build query strings themselves (at least in Sybase). But then there is nothing preventing you from building Transact SQL statements and sending that to the server on the fly to do this in Sybase either. -- CB -----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own