Re: [HACKERS] What about LIMIT in SELECT ?

Hi, my 2 cents...

I agree completely, LIMIT would be VERY usefull in web based apps, which
is all I run.  It does not matter to me if it is not part of a formal
standard.  The idea is so common that it is a defacto standard.

I would not expect it for this release, but could it get put on the TODO
list for next time?  I am even willing to work at an apprentise level on
this with a more expeireanced person that knows this stuff.

A note on implimentation:
I *used to* :) work with VFP on NT's :(
And the way VFP did LIMIT, it would only return the number of rows asked
for, BUT it still did the WHOLE search!
So on a larger table, which we had (property tax database for the county),
if some one put in too vague a query, it would try to collect ALL of the
rows as the initial result set, then give you the first x rows of that.

This did save on pushing mass amounts of data out to the browser, but it
would have been even better if it could have simply aborted the select
after having found x rows.

Also, it did not have the concept of an offset, so one could not select
100 rows, starting 200 rows in, which would be REALLY usefull for "paging"
through data.  I do not know if mySQL or any other has such a concept
either, but it would be nice.

So a properly implemented "LIMIT" could:
1.  Save pushing mass amounts of data across the web, that no one wants
any way.
2.  Stop vague queries from bogging down the server.
(On very larg tables this could be critical!)
3.  Enable "Paging" of data. (easyer then now (app. level))
4.  Would be a very nice feather in PostgreSQL's cap that could make it
even more attractive to those looking at all sorts of databases out there.

Have a great day.

On Tue, 13 Oct 1998, Oleg Bartunov wrote:

> Hi,
>
> I took a look at mysql and was very impressed with   possibility
> to limit number of rows returned from select. This is very useful
> feature for Web applications when user need to browse results of
> selection page by page. In my application I have to do full
> select every time user press button [Next] and show requested page
> using perl. This works more or less ok for several thousands rows but
> totally unusable for large selections. But now I'm about to work
> with big database and I don't know how I'll stay with postgres :-)
> It'll just doesn't work if customer will wait several minutes just browse
> next page. Mysql lacks some useful features postgres has
> (subselects, transaction ..) but for most Web applications I need
> just select :-) I dont' know how LIMIT is implemented in Mysql and
> I know it's not in SQL92 standart, but this makes Mysql very popular.
>
> Is it difficult to implement this feature in postgres ?
>
>     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
>
>

Terry Mackintosh <terry@terrym.com>          http://www.terrym.com
sysadmin/owner  Please! No MIME encoded or HTML mail, unless needed.

Proudly powered by R H Linux 4.2, Apache 1.3, PHP 3, PostgreSQL 6.3
-------------------------------------------------------------------
Success Is A Choice ... book by Rick Patino, get it, read it!