Forum Stats

  • 3,826,921 Users
  • 2,260,725 Discussions
  • 7,897,122 Comments

Discussions

GROUP BY 1,2;

BPeaslandDBA
BPeaslandDBA Member Posts: 4,615 Blue Diamond
edited May 7, 2020 7:25PM in Database Ideas - Ideas

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

BPeaslandDBActriebManish ChaturvediLothar FlatzJagadekaraPravin TakpireZlatko SiroticmarkmevansTom321vinaykumar2borneselArpit Jain -OracleJitendraSrinivasan Sa_nullbhagatsinghRobertOrtelabhinivesh.jainberxKiran PawarAish13KayKtop.gunAndreas HuberPkGeert Gruwez#Mayuruser12238076ApexBineGeeky NerdmanSergey KlimovN.B.user6192574rvstuckeMKJ10930279Rainer StenzelAndre SantosAparna Dutta-OracleBeGintrentnohup90cd643d-7f32-43ed-bdbf-371b64e32df8opentuningsGerald Venzl-Oracleandre.psantosAbhijit GourUser_ORA1Asdstuber
62 votes

Under Review - Voting Still Open · Last Updated

«13

Comments

  • abhinivesh.jain
    abhinivesh.jain Member Posts: 307 Blue Ribbon

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

  • TPD-Opitz
    TPD-Opitz Member Posts: 2,465 Silver Trophy
    edited Apr 3, 2015 12:32PM

    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 RobertsonApexBineJMURRAYSven W.
  • William Robertson
    William Robertson Member Posts: 9,567 Bronze Crown

    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.

    Thorsten Kettner
  • William Robertson
    William Robertson Member Posts: 9,567 Bronze Crown
    edited May 3, 2015 5:26AM

    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.
    Sven W. Member Posts: 10,535 Gold Crown
    edited May 4, 2015 7:28AM

    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

    TPD-OpitzBEDE
  • BPeaslandDBA
    BPeaslandDBA Member Posts: 4,615 Blue Diamond

    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

    TPD-OpitzKayKjnicholas330Thorsten Kettner
  • William Robertson
    William Robertson Member Posts: 9,567 Bronze Crown

    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
    top.gun Member Posts: 3,666 Gold Crown

    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.

    Gbenga Ajakaye
  • Sven W.
    Sven W. Member Posts: 10,535 Gold Crown

    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
    sysassysdba Member Posts: 459 Silver Badge
    edited May 11, 2015 6:38PM

    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 ;