This discussion is archived
1 2 Previous Next 20 Replies Latest reply: Dec 25, 2012 1:22 PM by sql_coder RSS

GROUP BY CUBE

sql_coder Newbie
Currently Being Moderated
Hello,

is there any difference between those two querries ?

SELECT columnA, columnB, COUNT(*)
FROM table1
GROUP BY CUBE(columnA, columnB)
;

SELECT columnA, columnB, COUNT(*)
FROM table1
GROUP BY CUBE(columnB, columnA)
;

I know it matters when using ROLLUP, but does the order of the columns has any impact in the GROUP BY CUBE statement ?
  • 1. Re: GROUP BY CUBE
    Frank Kulash Guru
    Currently Being Moderated
    Hi,

    No, those two queries are the same.

    When you run them, you may notice that order of rows in the output is different, but without an ORDER BY clause, the order of rows has no meaning.
  • 2. Re: GROUP BY CUBE
    sql_coder Newbie
    Currently Being Moderated
    hello Frank Kulash,

    thanks for the answer. by the way ROLLUP will sort a query without ORDER BY when not wrong...
  • 3. Re: GROUP BY CUBE
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    Ikrischer wrote:
    hello Frank Kulash,

    thanks for the answer. by the way ROLLUP will sort a query without ORDER BY when not wrong...
    Sorry, I don;t understand what you mean.

    Oracle does not guarantee the order of rows in any result set unless there is an ORDER BY clause (or CONNECT BY, but that's a separate topic).
    You may notice that, in a particular version, on a particular platform, the results of GROUP BY are always in some particular order, even though there is no ORDER BY clause. Don't depend on it. That's exactly the kind of behavior that may change in a different version. If you want the output to be in a specific order, then use an ORDER BY clause.
  • 4. Re: GROUP BY CUBE
    sql_coder Newbie
    Currently Being Moderated
    the resultset in a GROUP BY statement without an ORDER BY clause has no particular order, no doubt. but a I think the behaviour of GROUP BY ROLLUP is different....
  • 5. Re: GROUP BY CUBE
    sql_coder Newbie
    Currently Being Moderated
    I think when you use UNION in Oracle, you have an order without an ORDER BY clause as well.....
  • 6. Re: GROUP BY CUBE
    padders Pro
    Currently Being Moderated
    The point is not that the data was not implcitly ordered it is that it is not guaranteed and you should not rely on such behaviour.
  • 7. Re: GROUP BY CUBE
    sql_coder Newbie
    Currently Being Moderated
    Oracle is telling me, when using a UNION operator the result set is ordered by the first column. so why should I not relay on it ?
  • 8. Re: GROUP BY CUBE
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    Ikrischer wrote:
    Oracle is telling me, when using a UNION operator the result set is ordered by the first column. so why should I not relay on it ?
    Is Oracle really telling you that? Post a quote and a link to the relevant docuemntation.
    Do you mean that someone from Oracle is literally saying that directly to you, e.g., in correspondence regarding a Service Request? Post it, if you can.
  • 9. Re: GROUP BY CUBE
    sql_coder Newbie
    Currently Being Moderated
    Hello Frank,

    yes, it is in my Oracle documents I have for the SQL Expert exam. Not sure if you have access to it. I am not allowed to upload the document, but it is from Oracle. The following behaviour is for UNION, INTERCEPT and MINUS, not for UNION ALL.

    "By default, the output is sorted in ascending order of the first column of the SELECT clause."


    try this out:

    SELECT 2 Sort_Column, 'a test' Text_to_order FROM dual
    UNION
    SELECT 3, 'about Set Operators' FROM dual
    UNION
    SELECT 1, 'This is' FROM dual
    ;

    IKrischer

    Edited by: Ikrischer on Dec 16, 2012 6:27 PM
  • 10. Re: GROUP BY CUBE
    sql_coder Newbie
    Currently Being Moderated
    Hello Frank Kulash,

    are you able to have a look into the Oracle SQL expert documents, if not let me know please. And when I am wrong (what is always possible) I am willing to send you a very nice german beer as a late christmas present....

    ikrischer
  • 11. Re: GROUP BY CUBE
    Solomon Yakobson Guru
    Currently Being Moderated
    Ikrischer wrote:
    Oracle is telling me, when using a UNION operator the result set is ordered by the first column. so why should I not relay on it ?
    Wrong! Oracle never told you that. Oracle users (some of them posted that in various Oracle SQL expert documents) stated that. And it was true at the time. Why? because UNION implies unique rows and in older versions it was done by using sort. In newer (I believe starting 10.2) Oracle can use hash. Anyway, here is a link to Tom Type's blog. Pay attention, among other things, to:

    a) Do not ever count on the order of rows from a query unless you have an ORDER BY statement on your query!

    b) The only way to retrieve data from the database in some sorted order is to include an ORDER BY on your query. There is no substitute for ORDER BY.

    And most importantly to:

    Does UNION do a sort or is it the same as GROUP BY?

    Union is a distinct operation and <font color=red>may or may not sort in order to distinct</font>. In any case, the sort it does would be a <font color=red>binary sort, not a character set sort</font> (eg: does not sort the same as order by would necessarily). Throw in <font color=red>parallel operations, partitioning</font> and all bets are off._*

    SY.
  • 12. Re: GROUP BY CUBE
    sql_coder Newbie
    Currently Being Moderated
    Solomon Yakobson wrote:
    Ikrischer wrote:
    Oracle is telling me, when using a UNION operator the result set is ordered by the first column. so why should I not relay on it ?
    Wrong! Oracle never told you that.
    Before I got the Oracle documents to pass the exam 1Z0-047 (valid against 10g and 11g) I would agree, I never thought a query with a SET operator is sorted, I was surprised myself. But those documents (copyright Oracle Corporation) are telling me in the guidelines for SET operators, it is sorted by the fist column. I know an Index, etc. can just make it look like it is sorted, thats why I tried it with dual. So what do you want me to believe ? Do you have access to those documents yourself ?

    ikrischer
  • 13. Re: GROUP BY CUBE
    Solomon Yakobson Guru
    Currently Being Moderated
    Ikrischer wrote:
    I never thought a query with a SET operator is sorted, I was surprised myself.
    First of all SET is a function, not an operator. Secondly, query with SET function is not sorted. SET function apples to nested table, not to a query. I can write something like where SET is used as select-list expression:
    SQL> create or replace
      2    type SalList
      3      as table of number(7,2)
      4  /
    
    Type created.
    
    SQL> select  deptno,
      2          cast(collect(sal) as SalList) SalList
      3    from  emp
      4    group by deptno
      5  /
    
        DEPTNO SALLIST
    ---------- ---------------------------------------------
            10 SALLIST(2450, 5000, 1300)
            20 SALLIST(800, 3000, 800, 3000, 2975)
            30 SALLIST(1500, 1500, 1250, 1500, 1250, 1250)
    
    SQL>  select  deptno,
      2          set(cast(collect(sal) as SalList)) SalList
      3    from  emp
      4    group by deptno
      5  /
    
        DEPTNO SALLIST
    ---------- ------------------------------
            10 SALLIST(2450, 5000, 1300)
            20 SALLIST(800, 3000, 2975)
            30 SALLIST(1500, 1250)
    
    SQL> 
    SET eliminated duplicates, but do you see salary set sorted? Anyway, I think you are talking about:
    SELECT  *
      FROM  TABLE(SET(nested_table))
    /
    And this again will eliminate dups but not necessarily by sorting:
    SQL> select * from table(set(NumList(3,2,1,3,3,2,1,1,2)));
    
    COLUMN_VALUE
    ------------
               3
               2
               1
    
    SQL>  
    I have no idea if exam 1Z0-047 states that or not. I assume you misinterpreted what it states.

    SY.
  • 14. Re: GROUP BY CUBE
    sql_coder Newbie
    Currently Being Moderated
    Solomon Yakobson wrote:
    Ikrischer wrote:
    I never thought a query with a SET operator is sorted, I was surprised myself.
    First of all SET is a function, not an operator. Secondly, query with SET function is not sorted. SET function apples to nested table, not to a query. I can write something like where SET is used as select-list expression:
    But you know we talk about: http://docs.oracle.com/cd/E11882_01/server.112/e26088/operators005.htm#SQLRF51164 ?

    ikrischer
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points