Re: a question on SQL

Bruce Momjian wrote:
>
> Tom Lane wrote:
> > "Greg Sabino Mullane" <greg(at)turnstep(dot)com> writes:
> > >> It's the single most useful non-standard SQL feature postgresql has. It
> > >> is thus simultaneously bad (from a portatbility aspect) and brilliant
> > >> (because it's a million times easier and faster than the alternatives).
> >
> > > You mean second-most useful. LIMIT/OFFSET is the champion, hand down. :)
> >
> > Yeah, but that one's only quasi-non-standard ... several other DBMSes
> > have it too.
>
> I know MySQL has it, and SQL Lite added it. Which other ones? Someone
> asked me recently. I see this chart from Perl documentation:
>
> > http://search.cpan.org/~davebaird/SQL-Abstract-Limit-0.12/lib/SQL/Abstract/Limit.pm#DESCRIPTION
>
> Oh, and Rasmus Lerdorf told me he invented LIMIT for mSQL, and MySQL
> then added it, and that MySQL added the limit option.
>
> This was interesting in the MySQL manuals:
>
> For compatibility with PostgreSQL, MySQL also supports the LIMIT
> row_count OFFSET offset syntax.
>
> Did we add the OFFSET _keyword_. I remember we had the comma-ed numbers
> backwards, and we had OFFSET, but I thought that keyword came from
> MySQL. Obviously, they don't think so.

Informix provides the "FIRST" syntax to get the leading rows of a set; I think you have to use cursors to get further offsets though (been a while since I have had to use it), e.g. "SELECT FIRST 10 col1, col2, col3 FROM foo WHERE ...". No "LAST" either (just tried).

They have had this since at least IDS 8 and I thing the 7.x series had it as well. No idea where they got it from; I learned on Informix so I actually thought it was standard, until reality disabused me of the notion.

Greg Williamson
DBA
GlobeXplorer LLC