The Question :
231 people think this question is useful
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?
The Question Comments :
The Answer 1
260 people think this answer is useful
It means to group by the first column regardless of what it’s called. You can do the same with
The Answer 2
87 people think this answer is useful
SELECT account_id, open_emp_id
GROUP BY 1;
In above query
GROUP BY 1 refers to the
first column in select statement which is
You also can specify in
Note : The number in ORDER BY and GROUP BY always start with 1 not with 0.
The Answer 3
27 people think this answer is useful
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).
The Answer 4
14 people think this answer is useful
It will group by first field in the select clause
The Answer 5
5 people think this answer is useful
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
The Answer 6
4 people think this answer is useful
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
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.