2015-10-28 10:59 GMT+01:00 Rami Ojares <[email protected]>: > Not grouped by the title column. The title columns are aggregated via a > concatenation operation: string_agg() > > Does that mean that the select clause is interpreted before the having > clause? >
No, what made you think so? > To be more specific what does GROUP BY() mean? >> > > () is the empty GROUPING SET. The subtle difference between GROUP BY () > and no GROUP BY / HAVING at all is the fact that with GROUP BY (), you can > get zero rows. Without GROUP BY / HAVING, you will get at least one row. > > The SQL Server documentation explains this very nicely with examples: > https://technet.microsoft.com/en-us/library/bb522495(v=sql.105).aspx > > > I read the page but to me it seemed a little difficult to grasp. > Let's go over an example. > > TABLE: T1 > *-------*------* > | TITLE | YEAR | > *=======*======* > | abcdd | 1973 | > *-------*------* > | abxyz | 1973 | > *-------*------* > | ddefg | 1976 | > *-------*------* > > SELECT STRING_AGG(TITLE, ', ') > FROM T1 > WHERE TITLE LIKE 'ab%' > HAVING COUNT(*) > 1 > > According to you this returns: "abcdd, abxyz" > because first 2 rows are filtered by the where clause from T1. > Then that result is grouped using the aggregate operators in select clause. > No. The grouping takes place because of the implicit GROUP BY () clause > And finally having clause filters out those aggregated rows that do not > satify > having clauses condition applied to groupings before aggregation. > > Is this interpretation correct? > Almost. Questions that arises immediately is that what if GROUP BY clause does > exist. > Do we aggregate first using aggregate operators in select or group bu > clause? > When is the having in that case applied? > > Is the evaluation order (when order by exists) > 1) select, group by, having > 2) group by, select, having > 3) group by, having, select > The logical order is: FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> DISTINCT -> UNION (INTERSECT / EXCEPT) -> ORDER BY -> LIMIT / OFFSET > The actual order may depend on an optimiser's decisions, as long as the actual order is equivalent to the logical order. -- 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.
