3 ms·

It's actually the first time I'm seeing the `group by 1, 2` syntax.. is that Postgres-specific?

by cjauvin 10y ago

atwebb 10y ago
Don't believe you can do it in MSSQL, though an order by outer reference would work.
coredog64 10y ago
"group by 1,2" is for lazy people who never have to support an application in production. It tells the database to group by the columns in the order that they come back in the result and is supported by most engines.
- nimish 10y ago
  it'd be bad style to use it for saved queries but for experimentation it's great
- ak4g 10y ago
  Much like SELECT * or JOIN foo USING(bar), it's the kind of thing that greatly speeds up interactive queries, but shouldn't ever end up in source control.
  - JoeCamel 10y ago
    Why "JOIN foo USING (bar)" shouldn't be used?
    - ak4g 10y ago
      Because with USING, you're precluded from using the full table.column identifier in your JOIN condition. Without that, a future ALTER TABLE that adds another "bar" column to an existing table in the FROM-list will cause a previously-working query to break. https://gist.github.com/AdamG/1128b86fdafca1e53a4bd5253a6882a2 https://gist.github.com/AdamG/1128b86fdafca1e53a4bd5253a6882...
nickpeterson 10y ago
MSSQL doesn't support it, but does support the ORDER BY 1,2 syntax that is very similar. I don't believe GROUP BY n1,n2.. should be allowed, because it inverts the flow of control of the query. If you think about it, you're telling SQL to group by a column you haven't specified yet, using the order it appears in the SELECT clause. Someone could edit the query and add a new column at the front of the select list and completely change the meaning of the query. I think the ORDER BY clause has similar issues, but at least that occurs after the SELECT happens. tl;dr I shouldn't have to look at the ordering of the select to understand what has been grouped by. Order of SQL operations for the uninitiated, this explains why you can't, for instance, reference a column alias defined in a SELECT clause in the WHERE clause. 6 SELECT 1 FROM 2 JOIN 3 WHERE 4 GROUP BY 5 HAVING 7 ORDER BY

It's actually the first time I'm seeing the `group by 1, 2` syntax.. is that Postgres-specific?

by cjauvin 10y ago

It's actually the first time I'm seeing the `group by 1, 2` syntax.. is that Postgres-specific?

atwebb 10y ago

Don't believe you can do it in MSSQL, though an order by outer reference would work.

coredog64 10y ago

"group by 1,2" is for lazy people who never have to support an application in production. It tells the database to group by the columns in the order that they come back in the result and is supported by most engines.

nimish 10y ago
it'd be bad style to use it for saved queries but for experimentation it's great
ak4g 10y ago
Much like SELECT * or JOIN foo USING(bar), it's the kind of thing that greatly speeds up interactive queries, but shouldn't ever end up in source control.
- JoeCamel 10y ago
  Why "JOIN foo USING (bar)" shouldn't be used?
  - ak4g 10y ago
    Because with USING, you're precluded from using the full table.column identifier in your JOIN condition. Without that, a future ALTER TABLE that adds another "bar" column to an existing table in the FROM-list will cause a previously-working query to break. https://gist.github.com/AdamG/1128b86fdafca1e53a4bd5253a6882a2 https://gist.github.com/AdamG/1128b86fdafca1e53a4bd5253a6882...

nickpeterson 10y ago

MSSQL doesn't support it, but does support the ORDER BY 1,2 syntax that is very similar. I don't believe GROUP BY n1,n2.. should be allowed, because it inverts the flow of control of the query. If you think about it, you're telling SQL to group by a column you haven't specified yet, using the order it appears in the SELECT clause. Someone could edit the query and add a new column at the front of the select list and completely change the meaning of the query. I think the ORDER BY clause has similar issues, but at least that occurs after the SELECT happens. tl;dr I shouldn't have to look at the ordering of the select to understand what has been grouped by. Order of SQL operations for the uninitiated, this explains why you can't, for instance, reference a column alias defined in a SELECT clause in the WHERE clause. 6 SELECT 1 FROM 2 JOIN 3 WHERE 4 GROUP BY 5 HAVING 7 ORDER BY