News for hackers. Stuff that matters.
Re:Easy Steps to a Complete Understanding of SQL by jcampbell1 on Tuesday September 03, 2013 @09:49AM (#6320899)
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.
Re:Easy Steps to a Complete Understanding of SQL by chris_wot on Tuesday September 03, 2013 @09:58AM (#6320956)
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.
Re:Easy Steps to a Complete Understanding of SQL by jcampbell1 on Tuesday September 03, 2013 @10:46AM (#6321256)
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.

Re:Easy Steps to a Complete Understanding of SQL by lukaseder on Tuesday September 03, 2013 @10:56AM (#6321313)
(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

Skip to "Logical Processing Order of the SELECT statement".

MySQL, PostgreSQL, and SQLite behave differently, though!

Re:Easy Steps to a Complete Understanding of SQL by siddboots on Tuesday September 03, 2013 @10:35AM (#6321193)
Which RDBMS? This would not work on Oracle. You would need "group by WEEKDAY(created_at)" in this example.
Re:Easy Steps to a Complete Understanding of SQL by jcampbell1 on Tuesday September 03, 2013 @10:50AM (#6321279)
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.
Re:Easy Steps to a Complete Understanding of SQL by lukaseder on Tuesday September 03, 2013 @11:03AM (#6321351)
Aagh, it works indeed in PostgreSQL: 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.
Re:Easy Steps to a Complete Understanding of SQL by jcampbell1 on Tuesday September 03, 2013 @11:40AM (#6321554)
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.

Re:Easy Steps to a Complete Understanding of SQL by lukaseder on Wednesday September 04, 2013 @03:58AM (#6326213)
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.

Re:Easy Steps to a Complete Understanding of SQL by lukaseder on Tuesday September 03, 2013 @10:14AM (#6321071)
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...
Re:Easy Steps to a Complete Understanding of SQL by chris_wot on Tuesday September 03, 2013 @10:18AM (#6321092)
Only scalar expressions. You still need to retrieve the rows.