GROUP BY 1,2;

BPeaslandDBA

I can write a SQL like the following:

SELECT col1,col2,col3 FROM my_table ORDER BY 1,2;

We all know "1,2" is shorthand for the first and second columns in my SELECT clause.

My idea is to do something similar for the GROUP BY clause...

SELECT col1, col2, SUM(col3) FROM my_table GROUP BY 1,2;

Just an idea!
Brian

abhinivesh.jain

Looks interesting idea. I am wondering why such simple thing isn't in place since begining?

TPD-Opitz

unlike ORDER BY the GROUP BY clause is independend fron the selected columns.

I'd rather have

GROUP BY alias.*

so that this works:

SELECT emp.*,

  COUNT(*) AS subordinates_count

FROM employee emp

JOIN employee Sub

ON (emp.emp_id = Sub.superior_id)

GROUP BY emp.*

bye

TPD

William Robertson

Perhaps it would be nice if the GROUP BY clause could ignore column aliases. Inevitably you type "group by" and then copy and paste the non-aggregate part of the SELECT list, and then you have to go back and take out the column aliases.

William Robertson

I was about to suggest a GROUP BY ALL construction, but now that I check, other SQL dialects already use this for something else.

Sven W.

I don't like that Idea.

The real problem is the order in which programmers (and DBAs) write their select statements.

I propagate (and teach) that you should first think about the joins: e.g. where is your infomation coming from

Then think about the aggregation level: e.g. what should a single row represent

Then think about the columns in your select list incl. aggregation functions: e.g. what attributes do I want to see for that row.

Then think about the sorting/ordering of this output: e.g. ascending, descending, where to put the NULLs etc.

Using "GROUP BY 1,2" is nice for people who write the select first, without thinking first. Often this results in badly maintainable code.

Just imagine what happens if the next programmer desides that the first column (which could be a date column) should now be formatted to show Month names, instead of Months and years.

example:

select to_char(order_date, 'FMMonth YYYY') as order_date, order_type, count(*) order#

from myOrders

group by 1,2

BPeaslandDBA

I don't like that Idea.

The real problem is the order in which programmers (and DBAs) write their select statements.

I propagate (and teach) that you should first think about the joins: e.g. where is your infomation coming from

Then think about the aggregation level: e.g. what should a single row represent

Then think about the columns in your select list incl. aggregation functions: e.g. what attributes do I want to see for that row.

Then think about the sorting/ordering of this output: e.g. ascending, descending, where to put the NULLs etc.

Using "GROUP BY 1,2" is nice for people who write the select first, without thinking first. Often this results in badly maintainable code.

Just imagine what happens if the next programmer desides that the first column (which could be a date column) should now be formatted to show Month names, instead of Months and years.

example:

select to_char(order_date, 'FMMonth YYYY') as order_date, order_type, count(*) order#

from myOrders

group by 1,2

I wouldn't allow ORDER BY 1,2 in my maintainable, production ready code. This idea is for one-off SQL statements, not SQL to be put into production.

Cheers,
Brian

William Robertson

I wouldn't allow ORDER BY 1,2 in my maintainable, production ready code. This idea is for one-off SQL statements, not SQL to be put into production.

Cheers,
Brian

Of course. This would go in the "informal use only, not for production" section of the SQL manual

top.gun

Sure ORDER BY 1,2 could be poor programming, but it's allowed, so the precedent is there to allow it in the GROUP BY.

Sven W.

Sure ORDER BY 1,2 could be poor programming, but it's allowed, so the precedent is there to allow it in the GROUP BY.

I disagree. Order by is on a different level than group by.

The logical order of the clauses is :

AGGREGATION (GROUP BY) -> PROJECTION (SELECT clause) -> SORTING (ORDER BY)

The implications if something changes are also totally different.

Just because you allow numbers as placeholders in the order by list, does not make this valid in the group by and consequently also in the having clause.

sysassysdba

is not  it similar to group by rollup or group by cube?

http://docs.oracle.com/cd/B19306_01/server.102/b14223/aggreg.htm

example:

select count(*),DB_NAME,db_key,dbinc_key FROM RMAN.RC_ARCHIVED_LOG  GROUP BY rollup (DB_NAME,db_key,dbinc_key) ORDER BY 1 desc,db_name,db_key,dbinc_key ;