That is irrelevant. You said three rows, so three rows should be returned. The language should not attempt to implement wishy washy conditions guessing what you might have meant. If you wanted a maximum of three rows containing the SAME firstname, then that is what you would have asked for. SInce that is not what you asked for, returning eanything else is an error.
There is no guarantee that they will be the same three rows or that they will appear in the same order, if the same query is re-executed. There never is, unless both queries are performed in the same transaction. > -----Original Message----- > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users- > bounces at mailinglists.sqlite.org] On Behalf Of Darren Duncan > Sent: Sunday, 15 May, 2016 22:30 > To: SQLite mailing list > Subject: Re: [sqlite] Sqlite incompatibility with Postgres > > On 2016-05-15 12:35 AM, Simon Slavin wrote: > > On 15 May 2016, at 6:04am, Darren Duncan <darren at darrenduncan.net> > wrote: > > > >> You seem to be forgetting the fact that LIMIT/OFFSET is not its own > clause, rather it is an extension to the ORDER BY clause and only has > meaning within the context of the ORDER BY it is part of. > > > > 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 > > > > Without looking up either a standards document for SQL or the > documentation for your favourite implementations of SQL, answer this > question: > > > > Does the documentation for your favourite implementation of SQL state > that you'll get the same rows every time you execute the above "SELECT" ? > > 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. > > -- Darren Duncan > > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
