What does SQL clause GROUP BY 1 mean

Question

Someone sent me a SQL query where the GROUP BY clause consisted of the statement  GROUP BY 1   This must be a typo right  No column is given the alias 1  What could this mean  Am I right to assume that this must be a typo

User · Answer

In addition to grouping by the field name, you may also group by ordinal, or position of the field within the table. 1 corresponds to the first field (regardless of name), 2 is the second, and so on.

This is generally ill-advised if you're grouping on something specific, since the table/view structure may change. Additionally, it may be difficult to quickly comprehend what your SQL query is doing if you haven’t memorized the table fields.

If you are returning a unique set, or quickly performing a temporary lookup, this is nice shorthand syntax to reduce typing. If you plan to run the query again at some point, I’d recommend replacing those to avoid future confusion and unexpected complications (due to scheme changes).

User · Answer

It means to group by the first column regardless of what it s called   You can do the same with ORDER BY

User · Answer

It will group by the column position you put after the group by clause   for example if you run  SELECT SALESMAN NAME  SUM SALES  FROM SALES GROUP BY 1  it will group by SALESMAN NAME   One risk on doing that is if you run  Select    and for some reason you recreate the table with columns on a different order  it will give you a different result than you would expect

User · Answer

SELECT account id  open emp id                                     1           2  FROM account GROUP BY 1    In above query GROUP BY 1 refers to the first column in select statement which is    account id   You also can specify in ORDER BY      Note   The number in ORDER BY and GROUP BY always start with 1 not with 0

User · Answer

That means sql group by 1st column in your select clause  we always use this GROUP BY 1 together with ORDER BY 1  besides you can also use like this GROUP BY 1 2 3    of course it is convenient for us but you need to pay attention to that condition the result may be not what you want if some one has modified your select columns  and it s not visualized

User · Answer

It will group by first field in the select clause

[mysql] What does SQL clause "GROUP BY 1" mean?

Examples related to mysql

Examples related to sql

Examples related to group-by