> > All true. But it brings up a question. Suppose the following: > > > > first second > > ----- ------ > > Mark Spark > > Emily Spark > > Mary Soper > > Brian Soper > > > > SELECT first,second FROM members ORDER BY second LIMIT 3 > > > I think a proper solution for this then is to treat the LIMIT as approximate > rather than exact; it indicates a desire rather than a promise. > > In the scenario you describe, the query should return either 2 rows or 4 > rows, so that ALL of the rows whose second field value of "Spark" are, or are > not, returned. Projecting this to there not being an ORDER BY clause, either > all rows are returned or zero rows are returned. Thus the result is > deterministic. > > Whether returning above or below the limit is done, is a separate thing to > decide, though I suggest returning above is better.
I would say that this is an invalid query. As already applies for DISTINCT and GROUP BY, the query parser should require that every column in the column list should appear in the ORDER BY list. If it does not, then the result is indeterminate. Sqlite already permits indeterminate queries, but other SQL engines do not. Regards David M Bennett FACS Andl - A New Database Language - andl.org
