The main difference between HAVING and WHERE ist that WHERE operates on the input set and HAVING operates on the output set.
If your condition requires computing an aggregate, then HAVING is a viable method of avoiding a subquery that needs to repeatedly scan the input table: SELECT currency,price FROM Prices p WHERE day = (SELECT max(day) from Prices where currency=p.currency); explain query plan SELECT currency,price FROM Prices p WHERE day = (SELECT max(day) from Prices where currency=p.currency); sele order from deta ---- ------------- ---- ---- 0 0 0 SCAN TABLE Prices AS p (~500000 rows) 0 0 0 EXECUTE CORRELATED SCALAR SUBQUERY 1 1 0 0 SEARCH TABLE Prices USING AUTOMATIC COVERING INDEX (currency=?) (~1 rows) explain query plan SELECT currency, price FROM Prices GROUP BY currency HAVING day = max(day); sele order from deta ---- ------------- ---- ---- 0 0 0 SCAN TABLE Prices (~1000000 rows) 0 0 0 USE TEMP B-TREE FOR GROUP BY If your condition does not require computing an aggregate, just use WHERE. SELECT currency,price from Prices WHERE day=2; To find the latest price on or before day 3: SELECT currency,price from Prices WHERE day <= 3 GROUP by currency HAVING day = max(day); explain query plan SELECT currency,price from Prices WHERE day <= 3 GROUP by currency HAVING day = max(day); sele order from deta ---- ------------- ---- ---- 0 0 0 SCAN TABLE Prices (~333333 rows) 0 0 0 USE TEMP B-TREE FOR GROUP BY Note that this is without any defined indexes. Plans may change depending on indexes added, and the cardinality of constrained fields. -----Urspr?ngliche Nachricht----- Von: Rowan Worth [mailto:rowanw at dugeo.com] Gesendet: Dienstag, 29. September 2015 09:06 An: General Discussion of SQLite Database Betreff: [sqlite] Is it legal SQL to select a specific row of a group via a HAVING clause? Hi guys, Imagine a DB storing a history of currency exchange rate info. CREATE TABLE Prices ( id INTEGER PRIMARY KEY, day INTEGER, currency TEXT, price FLOAT); Assume 'day' increases monotonically and there is at most one price recorded per currency per day - if you want to know the latest prices, is this legal? SELECT currency, price FROM Prices GROUP BY currency HAVING time = MAX(time); I've seen this approach used and my reading of https://www.sqlite.org/lang_select.html#resultset hasn't left me confident that it is correct SQL, but in practice it seems to work fine... eg: insert into Prices (day, currency, price) values (1, 'EUR', 1.86); insert into Prices (day, currency, price) values (1, 'USD', 1.20); insert into Prices (day, currency, price) values (2, 'EUR', 1.92); insert into Prices (day, currency, price) values (2, 'USD', 1.18); insert into Prices (day, currency, price) values (3, 'USD', 1.24); /* price for EUR on day 3 intentionally left blank */ sqlite> SELECT currency, price FROM Prices GROUP BY currency HAVING day sqlite> = MAX(day); EUR|1.92 USD|1.24 MIN also seems to work: sqlite> SELECT currency, price FROM Prices GROUP BY currency HAVING day sqlite> = MIN(day); EUR|1.86 USD|1.2 As an aside, the query definitely doesn't work to get prices for a specific day: sqlite> SELECT currency, price FROM Prices GROUP BY currency HAVING day sqlite> = 2; EUR|1.92 I think the following explains the last result: > If a HAVING clause is a non-aggregate expression, it is evaluated with > respect to an arbitrarily selected row from the group. And the arbitrary row for EUR just happened to be day=2 here. But the documentation doesn't mention non-aggregate queries with a group by clause... > If the HAVING clause is an aggregate expression, it is evaluated > across all > rows in the group. I'm not sure how to interpret this in the context of the "day = MAX(day)" expression though - does "evaluated across all rows" mean that rows excluded by the clause are discarded? If not then I suspect this query is not legit... but I don't feel I've understood everything so any clarification is appreciated! Cheers, -Rowan _______________________________________________ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___________________________________________ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: hick at scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation.
