On 16 May 2016, at 5:29am, Darren Duncan <darren at darrenduncan.net> wrote:
> On 2016-05-15 12:35 AM, Simon Slavin wrote: > >> 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 What ? For a query on a 4 row table, where I have clearly and explicitly requested a limit of 3 rows, it can be correct to return 2 or 4 rows ? I would imagine that many programmers would find that annoying and counter-intuitive. To the other people who answered using the word 'deterministic', I would like them to answer my question: do you /know/ that your SQL engine is going to return the same three rows each time ? Or if no changes are made to the data while you execute the following two commands SELECT first,second FROM members ORDER BY second LIMIT 3; SELECT first,second FROM members ORDER BY second LIMIT 3 OFFSET 3 (or whatever the syntax is for your favourite implementation) are you sure you're going to get no rows twice ? Is it documented ? Simon.
