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?
Asked
Active
Viewed 2.2e+01k times
382
-
15Its not a typo, its the first column of your result set – Lamak Sep 12 '11 at 19:13
-
14Note that this syntax is non-portable. It will behave differently on databases other than mysql. In Oracle, for instance, it's treated as a constant. – Russell Reed Dec 19 '14 at 20:44
-
2@RussellReed Yes. unfortunately (since using an alias is sometimes very helpful) ansi sql does not allow grouping by column ordinal. The reason is that the group by happens before projection. But then .. what when we have grouping expressions with tens of lines .. we end up with .. *mutiples* of tens of lines in the final sql statement. – WestCoastProjects Nov 18 '15 at 19:56
6 Answers
436
It means to group by the first column of your result set regardless of what it's called. You can do the same with ORDER BY.
Yuck
- 49,664
- 13
- 105
- 135
-
20And it should never be used in production code unless you absolutely have to, eg. if you're dynamically selecting columns. It's hard to read and when someone modifies the select statement without also fixing the ordinals it can silently cause catastrophic issues. – Janne Annala Apr 08 '21 at 14:13
-
1This answer is not clear enough and still ambiguous as it is not clear what "first column" refers to. The table definition, or the select order. I will update it. – Joop Jul 01 '22 at 13:40
147
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.
wpercy
- 9,636
- 4
- 33
- 45
Vishwanath Dalvi
- 35,388
- 41
- 123
- 155
41
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).
vol7ron
- 40,809
- 21
- 119
- 172
-
19+1 for "don't do this" and I'd add that the best reason to avoid it is that it's not readable. – Yuck Sep 12 '11 at 19:29
-
+1 for the above; this is only (tmp) practical when you doing groupby all of the cols. If the table structure changes (order, or new fields added removed) this will cause problem – Neil Jan 20 '23 at 00:29
-
There is a nice discussion here as well: https://dba.stackexchange.com/a/86612/182712 – Neil Jan 20 '23 at 00:29
13
That means *"group by the 1st column in your select clause". Always use GROUP BY 1 together with ORDER BY 1.
You can also use GROUP BY 1,2,3... It is convenient, but you need to pay attention to that condition; the result may not be what you want if someone has modified your select columns and it's not visualized.
Connor Low
- 5,900
- 3
- 31
- 52
张艳军
- 241
- 4
- 5
-
4Care to elaborate on why to always use it in conjunction with order by? – Prince M Nov 22 '21 at 20:54
5
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.
