2015-10-27 8:51 GMT+01:00 Rami Ojares <[email protected]>: > Thanks Lukas! > It is always a pleasure to get these nuggets of important information from > the standard that I don't want to read myself. >
As far as I'm concerned, nothing beats a good glass of red wine whilst reading the SQL standard documents in front of the fireplace. Somehow, few people are with me on that ;) > Could you give me an example of what you can but in the having clause if > group by does not have any columns? > You can put all sorts of aggregate functions (and constants) in the HAVING clause. An example, querying the Sakila database (on PostgreSQL): SELECT string_agg(title, ', ') FROM film WHERE title LIKE 'AN%' HAVING count(*) > 5 "Return a concatenation of all the films starting with "AN", *IF* there are at least 5 such films." The above yields: ANACONDA CONFESSIONS, ANALYZE HOOSIERS, ANGELS LIFE, ANNIE IDENTITY, ANONYMOUS HUMAN, ANTHEM LUKE, ANTITRUST TOMATOES, ANYTHING SAVANNAH Increasing the predicate to HAVING count(*) > 10 ... will yield no result. The use-case is probably most interesting, when putting this sort of query in a (lateral) derived table and cross joining, i.e. when a certain aggregation threshold needs to be met to produce any rows (or no rows) from a JOIN. Hope this helps, Lukas -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
