Right but most if not all RDBMS support a form of LIMIT. It may be non standard but support is there.
On 5/14/07, Lance J. Andersen <[EMAIL PROTECTED]> wrote:
Also, there are not a lot of DBs that support that syntax... :-( David Van Couvering wrote: > Thanks for the tip, Bernt, but I must humbly say "yuck!" to the syntax. > > OK, getting over that, it's pretty worthless to me given that Derby > doesn't use it and Derby is the primary DB used by NetBeans. But > let's say it was implemented -- would it work with a result set that > is a join across multiple tables? I can't tell from the convoluted > syntax... > > Thanks, > > David > > On 5/14/07, Bernt M. Johnsen <[EMAIL PROTECTED]> wrote: >> >>>>>>>>>>>> David Van Couvering wrote (2007-05-14 09:13:28): >> > OK, so do I have it right that the right way to "hint" to the driver >> > to not cache all one million rows when I only need ten rows is to use >> > setMaxRows()? >> >> No. setFetchSize() is an optimization hint, setMaxRows() is a limit on >> the ResultSet size. A driver may or may not communicate this to the >> server, but the resultSet will never hold more than maxRows rows. >> >> > Is there a SQL standard way to "hint" to the server not to *process* >> > all one million rows (e.g. in the order by case)? >> >> There's a standard SQL way to ask for an exact number of rows in the >> query, like this >> >> SELECT * FROM ( >> SELECT >> ROW_NUMBER() OVER (ORDER BY key ASC) AS rownumber, >> columns >> FROM tablename >> ) AS foo >> WHERE rownumber <= n >> >> Look up in the SQL standard under "window functions" for more details. >> This is not implemented in Derby (Feature T611 Elementary OLAP >> operations http://wiki.apache.org/db-derby/SQLvsDerbyFeatures), >> >> > >> > Thanks, >> > >> > David >> > >> > On 5/14/07, Bernt M. Johnsen <[EMAIL PROTECTED]> wrote: >> > >What David wants, is the feature rgistered in >> > >https://issues.apache.org/jira/browse/DERBY-581 >> > > >> > >>>>>>>>>>>>> Craig L Russell wrote (2007-05-13 12:06:38): >> > >> >Also, how is maxrows related to the fetch size of a ResultSet? >> > >> >> > >> As I understand it, the fetch size relates to the number of rows >> > >> returned by the server to the client for each round trip to the >> > >> database. So theoretically the two numbers are independent. There's >> > >> no specified interaction except for the obvious one: requesting a >> > >> fetch size exceeding the maxrows doesn't make sense since there >> will >> > >> never be more than maxrows returned, and fetch size would >> effectively >> > >> be ignored. >> > > >> > >Fetch Size is in the JDBC spec defined to be an *optimization hint* >> > >from the application to the driver. It has no semantic meaning >> > >whatsoever, but may e.g. influence the number of rows prefetched per >> > >roundtrip and thus influence the overall performance of your >> > >application. >> > > >> > > >> > >-- >> > >Bernt Marius Johnsen, Database Technology Group, >> > >Staff Engineer, Technical Lead Derby/Java DB >> > >Sun Microsystems, Trondheim, Norway >> > > >> >> -- >> Bernt Marius Johnsen, Database Technology Group, >> Staff Engineer, Technical Lead Derby/Java DB >> Sun Microsystems, Trondheim, Norway >> >> -----BEGIN PGP SIGNATURE----- >> Version: GnuPG v1.4.2.2 (GNU/Linux) >> >> iD8DBQFGSLwalFBD9TXBAPARAjdsAJ9C1yWZCiA+G7kNwRVy81bzSQ/HsQCg2yDq >> KUloXCu1N+PcB6BIzkkKQpY= >> =RdA2 >> -----END PGP SIGNATURE----- >> >>
