Discussions
Categories
- 17.9K All Categories
- 3.4K Industry Applications
- 3.4K Intelligent Advisor
- 75 Insurance
- 537.7K On-Premises Infrastructure
- 138.7K Analytics Software
- 38.6K Application Development Software
- 6.1K Cloud Platform
- 109.6K Database Software
- 17.6K Enterprise Manager
- 8.8K Hardware
- 71.3K Infrastructure Software
- 105.4K Integration
- 41.6K Security Software
GROUP BY 1,2;

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
Comments
-
Looks interesting idea. I am wondering why such simple thing isn't in place since begining?
-
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
-
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.
-
I was about to suggest a GROUP BY ALL construction, but now that I check, other SQL dialects already use this for something else.
-
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 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 -
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,
BrianOf course. This would go in the "informal use only, not for production" section of the SQL manual
-
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.
-
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.
-
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 ;