S. Ramaswamy Wrote:
>
>Feature not available in SQL but extensions available depending on
>database !!
>
>1) MYSQL has a limit clause whereby you can say
>
> Select ...... LIMIT 5,10
>
> means start from 6th record and a total of 6+10 records
>
>2) ORACLE has the maximum rows available but not start from feature.
>However I do not know the exact syntax
>
>3) If you are using JDBC, then you have setMaxRows(int num) that allows
>you to limit total number of rows to be returned. The other rows are
>silently dropped.
With Informix, the syntax to return at most 10 rows is:
SELECT START 10 * from my_table;
--------
^
There is also a MIDDLE specifier that returns a middle number of rows.
By setting a limit on the number of rows returned you can significantly
improve the response time for a query. I have found this very useful when
displaying SQL search results w/ a web interface. If you are only going to
show 10-20 records on a page for a query that returns 100's of records,
there is no sense waiting for a query to return all of the records.
-Richard
-----Original Message-----
From: S.Ramaswamy [mailto:[EMAIL PROTECTED]]
Sent: Friday, December 17, 1999 8:28 PM
To: [EMAIL PROTECTED]
Subject: Re: OFF TOPIC: SQL
Giovanni Az�a Garc�a wrote:
>
> Hello all,
>
> I have worked a lot with SQL but I would like to know
> how could I limit the number of rows one SELECT returns?
>
> For example, if I had a table with Customers having for each
> how much money they have spent in my company and I wanted to
> know the 5 customers that have spent the most, *ONLY* five:
>
> SELECT *
> FROM CUSTOMER_TABLE
> ORDER BY MONEY_SPENT; // This would return all rows ordered
>
> How could I limit this using SQL specifying the ammount of rows in
advance?
>
> I mean without having to iterate over all existing records.
> The only approach I have found is using the HAVING option so I could say
> HAVING MONEY_SPENT > X but how can I determine a X for that there
> are only Y records.
>
> Thanks in advance,
> Giovanni
>
> PD: Maybe I'm expecting too much from SQL
Feature not available in SQL but extensions available depending on
database !!
1) MYSQL has a limit clause whereby you can say
Select ...... LIMIT 5,10
means start from 6th record and a total of 6+10 records
2) ORACLE has the maximum rows available but not start from feature.
However I do not know the exact syntax
3) If you are using JDBC, then you have setMaxRows(int num) that allows
you to limit total number of rows to be returned. The other rows are
silently dropped.
==--==--==--==--==--==--==--==--==--==--==--==--==--==
S.Ramaswamy
Matrix Infotech Syndicate
D-7, Poorti, Vikaspuri, New Delhi, 110018, India
PHONE: +91-11-5610050, FAX: +91-11-5535103
WEB http://MatrixInfotech.HyperMart.Net
==--==--==--==--==--==--==--==--==--==--==--==--==--==
===========================================================================
To unsubscribe: mailto [EMAIL PROTECTED] with body: "signoff
JSP-INTEREST".
FAQs on JSP can be found at:
http://java.sun.com/products/jsp/faq.html
http://www.esperanto.org.nz/jsp/jspfaq.html
Richard Yee.vcf
