What do comma-separated integers in a GROUP BY statement accomplish?

I have a query like this:

SELECT col1, col2, col3, col4, col5, SUM(col6) AS tot开发者_开发百科al
FROM table_name
WHERE col1 < 99999
GROUP BY 1,2,3,4,5

What does the GROUP BY statement actually accomplish here? The query does not work properly without the comma-separated integers.

It is equivalent to writing:

SELECT col1, col2, col3, col4, col5, SUM(col6) AS total
  FROM table_name
 WHERE col1 < 99999
 GROUP BY col1, col2, col3, col4, col5

The numbers are the values/columns in the select-list expressed by ordinal position in the list, starting with 1.

The numbers used to mandatory; then the ability to use the expressions in the select-list was added. The expressions can get unwieldy, and not all DBMS allow you to use 'display labels' or 'column aliases' from the select-list in the GROUP BY clause, so occasionally using the column numbers is helpful.

In your example, it would be better to use the names - they are simple. And, in general, use names rather than numbers whenever you can.

My guess is that your database product allows for referencing columns in the Group By by position as opposed to only by column name (i.e., 1 for the first column, 2 for the second column etc.) If so, this is a proprietary feature and is not recommended because of portability and (arguably) readability issues (But can admittedly be handy for a quick and dirty query).

Tried kind a same query in MS SQL Server 2005

select distinct host from some_table group by 1,2,3

It error's out saying

Each GROUP BY expression must contain at least one column that is not an outer reference.

So this indicates that those 1,2,3 are nothing but column outer referrence