On 21 Jun 2010, at 3:16am, P Kishor wrote: > The above brings up a related issue... At least Pg docs suggest that > LIMIT should always be used with ORDER BY. See > http://developer.postgresql.org/pgdocs/postgres/sql-select.html > > "When using LIMIT, it is a good idea to use an ORDER BY clause that > constrains the result rows into a unique order. Otherwise you will get > an unpredictable subset of the query's rows — you might be asking for > the tenth through twentieth rows, but tenth through twentieth in what > ordering? You don't know what ordering unless you specify ORDER BY." > > Which implies, the WHERE clause will be applied, then the ORDER BY > will be applied on the entire set (because the returned set might not > be in the desired order), and then, LIMIT will be applied. So, that in > turn implies that by the time LIMIT is applied, the result set is > already out of the bag, it has already been calculated.
That is not really a useful recommendation. Because even an ORDER BY clause may not definitively set the order of the returned hits. For instance, one might have SELECT * FROM invoices ORDER BY invoiceDate LIMIT 5 However, there may be three invoices on the first date and three on the second date. Unless Postgres has an understood but unwritten rule that unspecified orders are supplanted by primary key order (common in SQL implementations) the ORDER BY clause here does not clarify which records the LIMIT 5 would return. One might as well have no ORDER BY clause at all. MySQL (the implementation of SQL I'm most familiar with) has a complicated set of caches and optimizations and one of the results is that if you don't specify an unambiguous ORDER you can get records back in a different order for each SELECT command. So any routine which tries to return page 1 of results, then page 2, then page 3, absolutely /must/ specify an unambiguous order for the records or it won't work properly. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
