News for hackers. Stuff that matters.
Re:The SQL filter clause: selective aggregates by MichaelBurge on Monday May 30, 2016 @09:45AM (#11801297)
One reason is to remind you of the SQL order-of-operations:

* The from clause retrieves records from data sources like set functions, tables, or views.

* The on/using clause joins tables

* The where clause filters out records

* The group by clause assigns records to buckets

* The having clause filters out records

* The select clause runs

* The order by sorts

* The limit clause picks records

In this query:

> select sum(1) over () from (select 1 a) x join (select 1 a) y using (a) where 1 = 1 group by true having true order by 1 limit 1;

The only out-of-place component is the select and window function(which runs after the group by clause). In a more complex query with multiple selects in scope, the order of terms helps remind you which parts are running in which order.

Note that the optimizer won't strictly enforce the order; it's just a guide to interpret what the results should be.

If you need to do something out-of-order, you can use a subselect:

> select * from (select sum(1) over () from (select 1 a) x join (select 1 a) y using (a) where 1 = 1 group by true having true limit 1 ) x order by 1;

Here, I've swapped the limit clause and the order-by clause, although you can still see the order of operations in the explain plan being separated by a 'Subquery scan'.

Re:The SQL filter clause: selective aggregates by kbenson on Monday May 30, 2016 @02:12PM (#11802390)
> The having clause filters out records

I think you mean "The having clause filters out buckets", right?