Unfortunately I'm not intemately familiar with the internal workings of Derby but, I would think if you had the appropriate meta-data in the internal table structures, this would be a very efficient place to execute this. But, I suppose order by/ResultSet counting would work as well.
Thank you for the info Jeremy! Brian Abbott ----- Original Message ----- From: "Jeremy Boynes" <[EMAIL PROTECTED]> To: "Derby Development" <[email protected]> Sent: Saturday, November 27, 2004 12:31 PM Subject: Re: Support for SQL Limit? > Brian Abbott wrote: > > As far as I can tell, Derby does not currently support the SQL Limit > > command. Am I missing something? If it doesnt, are there any plans to > > implement it? > > > > LIMIT is really a MySQL feature not an SQL command. Different databases > implement this in different ways as it was not standardized until SQL03 > (with window functions that I don't think anyone implements yet and > which are really there for OLAP processing). > > One problem with it is that SQL does not define the order in which rows > are returned; even order by will not define the order for rows with the > same sort key. This means that if you execute the same query twice you > may get the rows returned in different orders - this is common on any > database that uses a parallel query algorithm. As a result the data > returned by LIMIT/OFFSET may well be non-deterministic. > > Subject to these constraints, you can mimic this on the client side by > controlling how you iterate over the ResultSet - e.g. skip offset rows, > process limit rows and then close it. This actually gives you a solution > that is portable between database vendors. > > The downside is that the offset rows need to be evaluated and returned > to the client only to be discarded. However, unless offset is very large > (which would imply that perhaps you should look for a better query > strategy anyway) this is probably not where the majority of your > processing effort is going. Further, if you are using Derby embedded > then you are simply duplicating what the server would need to do to > implement offset anyway so the difference is going to be minimal. > > However, if you feel that LIMIT/OFFSET is critical or useful > functionality you might want to open a Jira item to add it as an > enhancement. > > -- > Jeremy
