Problem
Someone submitted me a SQL query with the following statement in the GROUP BY clause: GROUP BY 1.
Isn’t it obvious that this is a mistake? The alias 1 is not assigned to any column. What does this imply? Is it correct to assume that this is a mistake?
Asked by Spencer
Solution #1
It simply means to sort by the first column, regardless of its name. ORDER BY allows you to do the same thing.
Answered by Yuck
Solution #2
SELECT account_id, open_emp_id
^^^^ ^^^^
1 2
FROM account
GROUP BY 1;
The first column in the select statement, account id, is referred to as GROUP BY 1 in the given query.
You can also select ORDER BY.
Answered by Vishwanath Dalvi
Solution #3
You can also group by ordinal, or the location of the field within the table, in addition to grouping by field name. The first field (independent of name) is 1, the second is 2, and so on.
If you’re grouping on something specific, this is generally not a good idea because the table/view structure may change. Furthermore, if you haven’t remembered the database fields, it may be tough to rapidly know what your SQL query is doing.
This is an useful shorthand syntax to reduce typing if you’re returning a unique set or conducting a brief lookup. I’d recommend replacing those if you want to run the query again in the future to avoid further confusion and issues (due to scheme changes).
Answered by vol7ron
Solution #4
In the select clause, it will group by the first field.
Answered by Daan Geurts
Solution #5
*”group by the first column in your select clause,” it means. GROUP BY 1 should always be used in conjunction with ORDER BY 1.
GROUP BY 1,2,3… is another option. It’s convenient, but you should be aware of that condition; if someone has changed your pick columns and it hasn’t been visualized, the result may not be what you want.
Answered by 张艳军
Post is based on https://stackoverflow.com/questions/7392730/what-does-sql-clause-group-by-1-mean