3 ms·

I think the author has the order wrong. Consider: SELECT WEEKDAY(created_at) wkday, COUNT(1) FROM orders GROUP BY wkday The above query works fi

I think the author has the order wrong. Consider: SELECT WEEKDAY(created_at) wkday, COUNT(1) FROM orders GROUP BY wkday The above query works fine, thus GROUP BY, ORDER BY, and HAVING are all aware of SELECT.

lukaseder 13y ago
True, the explanation was maybe a bit simplistic. Some expressions are allowed to appear even if they're not declared in the GROUP BY clause...
- chris_wot 13y ago
  Only scalar expressions. You still need to retrieve the rows.
chris_wot 13y ago
Those are expressions. They only work once you have picked the table(s) - FROM - and determined the rows you want returned - WHERE. The query then groups and orders the results and calculates the scalar value of COUNT(1) which it repeats on every row, then it determines the weekday for every row for the column created_at and uses that value for output.
- jcampbell1 13y ago
  My point is that a better explanation of the execution ordering is: FROM, JOIN, WHERE, SELECT, GROUP BY, HAVING, DISTINCT, UNION, ORDER BY, LIMIT. The author has SELECT after the GROUP BY and HAVING, which doesn't make sense because GROUP BY is aware of expressions in the SELECT statement. Grouping and filtering happens after selection.
  - lukaseder 13y ago
    (I had to correct myself) SELECT is evaluated after GROUP BY, at least in most databases that implement SQL correctly. SQL Server's documentation nicely explains this: http://technet.microsoft.com/en-us/library/ms189499.aspx http://technet.microsoft.com/en-us/library/ms189499.aspx Skip to "Logical Processing Order of the SELECT statement". MySQL, PostgreSQL, and SQLite behave differently, though!
siddboots 13y ago
Which RDBMS? This would not work on Oracle. You would need "group by WEEKDAY(created_at)" in this example.
- jcampbell1 13y ago
  MySQL and Postgres. You can use the source tables, anything in the select statement, or an ordinal position in the select statement. In the example "GROUP BY 1", "GROUP BY wkday", and "GROUP BY WEEKDAY(created_at)" are all the same.
  - lukaseder 13y ago
    Aagh, it works indeed in PostgreSQL: http://sqlfiddle.com/#!12/d41d8/1585 http://sqlfiddle.com/#!12/d41d8/1585 WITH orders(created_at) AS ( SELECT date '2012-01-01' UNION ALL SELECT date '2012-01-02' UNION ALL SELECT date '2012-01-03' UNION ALL SELECT date '2012-01-04' UNION ALL SELECT date '2012-01-05' UNION ALL SELECT date '2012-01-06' UNION ALL SELECT date '2012-01-07' UNION ALL SELECT date '2012-01-08' ) SELECT EXTRACT(DOW FROM created_at) wkday, COUNT(1) FROM orders GROUP BY wkday That's really disturbing. I fixed the linked article accordingly.
    - jcampbell1 13y ago
      You fixed the article incorrectly. SELECT aliases are never available in a WHERE clause. Edit: it seems more complicated that I initially assumed. MySQL doesn't allow it. SQLite does.
      - lukaseder 13y ago
        You're right, the new wording was ambiguous. A better wording is this: Rule number 2, for instance, does not apply exactly in the above way to MySQL, PostgreSQL, and SQLite.

lukaseder 13y ago

True, the explanation was maybe a bit simplistic. Some expressions are allowed to appear even if they're not declared in the GROUP BY clause...

chris_wot 13y ago
Only scalar expressions. You still need to retrieve the rows.

chris_wot 13y ago

Those are expressions. They only work once you have picked the table(s) - FROM - and determined the rows you want returned - WHERE. The query then groups and orders the results and calculates the scalar value of COUNT(1) which it repeats on every row, then it determines the weekday for every row for the column created_at and uses that value for output.

jcampbell1 13y ago
My point is that a better explanation of the execution ordering is: FROM, JOIN, WHERE, SELECT, GROUP BY, HAVING, DISTINCT, UNION, ORDER BY, LIMIT. The author has SELECT after the GROUP BY and HAVING, which doesn't make sense because GROUP BY is aware of expressions in the SELECT statement. Grouping and filtering happens after selection.
- lukaseder 13y ago
  (I had to correct myself) SELECT is evaluated after GROUP BY, at least in most databases that implement SQL correctly. SQL Server's documentation nicely explains this: http://technet.microsoft.com/en-us/library/ms189499.aspx http://technet.microsoft.com/en-us/library/ms189499.aspx Skip to "Logical Processing Order of the SELECT statement". MySQL, PostgreSQL, and SQLite behave differently, though!

siddboots 13y ago

Which RDBMS? This would not work on Oracle. You would need "group by WEEKDAY(created_at)" in this example.

jcampbell1 13y ago
MySQL and Postgres. You can use the source tables, anything in the select statement, or an ordinal position in the select statement. In the example "GROUP BY 1", "GROUP BY wkday", and "GROUP BY WEEKDAY(created_at)" are all the same.
- lukaseder 13y ago
  Aagh, it works indeed in PostgreSQL: http://sqlfiddle.com/#!12/d41d8/1585 http://sqlfiddle.com/#!12/d41d8/1585 WITH orders(created_at) AS ( SELECT date '2012-01-01' UNION ALL SELECT date '2012-01-02' UNION ALL SELECT date '2012-01-03' UNION ALL SELECT date '2012-01-04' UNION ALL SELECT date '2012-01-05' UNION ALL SELECT date '2012-01-06' UNION ALL SELECT date '2012-01-07' UNION ALL SELECT date '2012-01-08' ) SELECT EXTRACT(DOW FROM created_at) wkday, COUNT(1) FROM orders GROUP BY wkday That's really disturbing. I fixed the linked article accordingly.
  - jcampbell1 13y ago
    You fixed the article incorrectly. SELECT aliases are never available in a WHERE clause. Edit: it seems more complicated that I initially assumed. MySQL doesn't allow it. SQLite does.
    - lukaseder 13y ago
      You're right, the new wording was ambiguous. A better wording is this: Rule number 2, for instance, does not apply exactly in the above way to MySQL, PostgreSQL, and SQLite.