Results 1 to 10 of 10
  1. #1
    Join Date
    May 2003
    Location
    Stockholm, Sweden
    Posts
    15

    Question Unanswered: Which columns must be included in GROUP BY section?

    I only have MySQL db's currently available for testing. Does anybody know if the following SQL is valid in Oracle, or would it create a ORA-00979? Is it different for different versions of Oracle?

    SELECT c1, COUNT(c2) AS x FROM t GROUP BY c1 ORDER BY c3

    (The interesting part is if "c3" must be part of the GROUP BY section too rather than just "c1". The code above works on MySQL.)

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    how about including the c3 column in the select statement?
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    May 2003
    Location
    Stockholm, Sweden
    Posts
    15
    Originally posted by The_Duck
    how about including the c3 column in the select statement?
    Yes, I could do that -- and then it would be obvious that it is needed in the GROUP BY section. By I have no use for the value of c3, I just want to order by it. (I'm trying to optimize performance and result set size, my real world SQL code is much more complex than my example above)

  4. #4
    Join Date
    Feb 2004
    Location
    St. Louis
    Posts
    2
    Originally posted by The_Duck
    how about including the c3 column in the select statement?
    I'm not saying that using c3 in your statement is the only way to do it, but it will work if you do.

  5. #5
    Join Date
    Feb 2004
    Location
    St. Louis
    Posts
    2

    Re: Which columns must be included in GROUP BY section?

    Originally posted by rydenius
    I only have MySQL db's currently available for testing. Does anybody know if the following SQL is valid in Oracle, or would it create a ORA-00979? Is it different for different versions of Oracle?

    SELECT c1, COUNT(c2) AS x FROM t GROUP BY c1 ORDER BY c3

    (The interesting part is if "c3" must be part of the GROUP BY section too rather than just "c1". The code above works on MySQL.)
    Test in this statement, it's using the group by that is causing your issue.

    Using just the order by c3 in your statement and not including it in your selected columns does work, it's when you combine the group by that you encounter the error.

    Sorry to say, i don't have a solution off the top of my head.

  6. #6
    Join Date
    Feb 2002
    Location
    iceland
    Posts
    15
    The column on which Aggreagete Function is executed must be grouped by, if necessary.
    so:
    SELECT c1, COUNT(c2) AS x FROM t GROUP BY c2 ORDER BY c3

    ORDER BY c3 here is not needed, but i think will not cause problems...

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    In A GROUP BY query, you cannot ORDER BY something that you didn't SELECT - it just isn't allowed. The ORDER BY is applied after the data has been grouped, by which time any columns not selected simply "aren't there".

    You could do this:

    SELECT c1, COUNT(c2) AS x
    FROM
    ( SELECT c1, c2 FROM t ORDER BY c3
    )
    GROUP BY c1;

    ... but I would not expect it to improve performance.

  8. #8
    Join Date
    May 2003
    Location
    Stockholm, Sweden
    Posts
    15
    Originally posted by andrewst
    In A GROUP BY query, you cannot ORDER BY something that you didn't SELECT - it just isn't allowed. The ORDER BY is applied after the data has been grouped, by which time any columns not selected simply "aren't there".

    You could do this:

    SELECT c1, COUNT(c2) AS x
    FROM
    ( SELECT c1, c2 FROM t ORDER BY c3
    )
    GROUP BY c1;

    ... but I would not expect it to improve performance.
    Thanks a lot! So my example is MySQL specific SQL extension. Hmm, I'm not that surprised. I guess they regard the extra column(s) ordered by as implicit both in the select and the group by sections and then throw away the extra column(s) before returning the result set. (The reason I asked in the first place was that the MySQL version I used actually gained performance by omitting stuff in the group by section)

  9. #9
    Join Date
    May 2003
    Location
    Stockholm, Sweden
    Posts
    15
    Originally posted by commageneus
    The column on which Aggreagete Function is executed must be grouped by, if necessary.
    so:
    SELECT c1, COUNT(c2) AS x FROM t GROUP BY c2 ORDER BY c3

    ORDER BY c3 here is not needed, but i think will not cause problems...
    Group by c2 ??? That sounds _very_ odd. And why wouldn't "order by c3" be needed if I want the selection sorted that way. I think I'll listen to andrewst instead. Well, thanks anyway.

  10. #10
    Join Date
    May 2003
    Location
    Stockholm, Sweden
    Posts
    15
    Found some documentation of this MySQL extension:
    http://www.mysql.com/doc/en/GROUP-BY-hidden-fields.html

    Thanks, especially to andrewst.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •