Home » SQL & PL/SQL » SQL & PL/SQL » Group By Clause with Order By Clause (Oracle 9i)
Group By Clause with Order By Clause [message #386373] Sat, 14 February 2009 02:01 Go to next message
ygsunilkumar
Messages: 100
Registered: August 2008
Senior Member
When GROUP BY clause is used with one or more columns,the results will be sorted(ascending) order of GROUP BY columns (by default) so why ORDER BY clause is required for sorting? Please explain.
Re: Group By Clause with Order By Clause [message #386378 is a reply to message #386373] Sat, 14 February 2009 02:28 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Don't multipost. I deleted three duplicate posts.

GROUP BY can have a sort as a side-effect, but it is not guaranteed to order. It all depends on the path the optimizer chooses.
ORDER BY is the only way to order resultsets.



[disclaimer: I am aware that there are some other obscure ways, but they are not applicable to the level of this question]
Re: Group By Clause with Order By Clause [message #386379 is a reply to message #386378] Sat, 14 February 2009 02:42 Go to previous messageGo to next message
ygsunilkumar
Messages: 100
Registered: August 2008
Senior Member
Can you please explain in detail. I couldnot get the side-effect.
Re: Group By Clause with Order By Clause [message #386380 is a reply to message #386379] Sat, 14 February 2009 02:48 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
You noticed and described the side-effect yourself!
There is nothing to explain, it is as simple as this: if you want a resultset ordered, use ORDER BY. Full stop.
Re: Group By Clause with Order By Clause [message #386425 is a reply to message #386379] Sat, 14 February 2009 16:21 Go to previous messageGo to next message
gintsp
Messages: 118
Registered: February 2007
Senior Member
Actually the side effect was because till 8i (if I'm not mistaken) Oracle had sort group by operation to produce group by results. Starting from 9i it has hash group by, which is usually now more common operation. So that was the reason why most of the time it was a wronggggggg assumption that group by also sorts data. Oracle never promised that, even more it said the only way to guarantee order is order by clause. So don't be one of the horde to catch the same mistake Wink
Re: Group By Clause with Order By Clause [message #386455 is a reply to message #386373] Sun, 15 February 2009 03:25 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Here's an example; I'll create a table, insert some data and write two queries - one without and one with the ORDER BY clause.
SQL> CREATE TABLE TEST (id NUMBER, cnt NUMBER);

Table created.

SQL> INSERT ALL
  2    INTO TEST (id, cnt) VALUES (4, 40)
  3    INTO TEST (id, cnt) VALUES (2, 20)
  4    INTO TEST (id, cnt) VALUES (3, 30)
  5    INTO TEST (id, cnt) VALUES (1, 10)
  6    INTO TEST (id, cnt) VALUES (2, 20)
  7    INTO TEST (id, cnt) VALUES (4, 40)
  8  SELECT * FROM dual;

6 rows created.

OK, now let's do some querying:
SQL> SELECT id, SUM(cnt) sum_cnt
  2  FROM TEST
  3  GROUP BY id;

        ID    SUM_CNT
---------- ----------
         1         10
         2         40
         4         80
         3         30
Huh? IDs aren't exactly ordered, are they? So let's see what ORDER BY does:
SQL> SELECT id, SUM(cnt) sum_cnt
  2  FROM TEST
  3  GROUP BY id
  4  ORDER BY id;

        ID    SUM_CNT
---------- ----------
         1         10
         2         40
         3         30
         4         80

SQL>

A conclusion: if you want results ordered by certain column(s), use ORDER BY clause.

Happy?
Re: Group By Clause with Order By Clause [message #386499 is a reply to message #386373] Sun, 15 February 2009 22:49 Go to previous messageGo to next message
m_golam_hossain
Messages: 89
Registered: August 2008
Location: Uttara, Dhaka, Bangladesh
Member

Mr. Sunil Kumar,

Perhaps you are trying to use the experience of XBase Programming like dBase, FoxPro, etc.

But, it's Oracle. So, you need to add Order By Clause with Group By Clause.

Thanks-

Mohd. Golam Hossain
Uttara, Dhaka, Bangladesh.

[Updated on: Sun, 15 February 2009 22:55]

Report message to a moderator

Re: Group By Clause with Order By Clause [message #387003 is a reply to message #386455] Tue, 17 February 2009 09:58 Go to previous messageGo to next message
ygsunilkumar
Messages: 100
Registered: August 2008
Senior Member
Mr. Littlefoot, I tried with example below, I am getting the output in order by using Group By clause without using order by clause. How?

CREATE TABLE test_order_order (id NUMBER, cnt NUMBER);

Table created.

INSERT ALL
INTO test_order_order (id, cnt) VALUES (4, 40)
INTO test_order_order (id, cnt) VALUES (2, 20)
INTO test_order_order (id, cnt) VALUES (3, 30)
INTO test_order_order (id, cnt) VALUES (1, 10)
INTO test_order_order (id, cnt) VALUES (2, 20)
INTO test_order_order (id, cnt) VALUES (4, 40)
INTO test_order_order (id, cnt) VALUES (5, 40)
INTO test_order_order (id, cnt) VALUES (6, 40)
INTO test_order_order (id, cnt) VALUES (7, 40)
INTO test_order_order (id, cnt) VALUES (8, 40)
INTO test_order_order (id, cnt) VALUES (9, 40)
INTO test_order_order (id, cnt) VALUES (10, 60)
INTO test_order_order (id, cnt) VALUES (11, 120)
INTO test_order_order (id, cnt) VALUES (12, 130)
INTO test_order_order (id, cnt) VALUES (13, 140)
INTO test_order_order (id, cnt) VALUES (14, 90)
INTO test_order_order (id, cnt) VALUES (15, 40)
INTO test_order_order (id, cnt) VALUES (16, 40)
INTO test_order_order (id, cnt) VALUES (17, 70)
INTO test_order_order (id, cnt) VALUES (18, 95)
INTO test_order_order (id, cnt) VALUES (19, 40)
INTO test_order_order (id, cnt) VALUES (20, 35)
INTO test_order_order (id, cnt) VALUES (21, 65)
INTO test_order_order (id, cnt) VALUES (22, 62)
INTO test_order_order (id, cnt) VALUES (23, 72)
INTO test_order_order (id, cnt) VALUES (24, 80)
INTO test_order_order (id, cnt) VALUES (25, 20)
select * from dual;


SELECT id, SUM(cnt) sum_cnt
FROM test_order
GROUP BY id;

SQL> SELECT id, SUM(cnt) sum_cnt
  2  FROM test_order
  3  GROUP BY id;

        ID    SUM_CNT
---------- ----------
         1         10
         2         40
         3         30
         4         80
         5         40
         6         40
         7         40
         8         40
         9         40
        10         60
        11        120

        ID    SUM_CNT
---------- ----------
        12        130
        13        140
        14         90
        15         40
        16         40
        17         70
        18         95
        19         40
        20         35
        21         65
        22         62

        ID    SUM_CNT
---------- ----------
        23         72
        24         80
        25         20

25 rows selected.
Re: Group By Clause with Order By Clause [message #387013 is a reply to message #387003] Tue, 17 February 2009 10:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I am getting the output in order by using Group By clause without using order by clause. How?

Why not?

Regards
Michel
Re: Group By Clause with Order By Clause [message #387018 is a reply to message #387003] Tue, 17 February 2009 10:35 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@ygsunilkumar,

Based on your examples only I am arriving at the same result as Littlefoot got:

SQL> SELECT id, SUM(cnt) sum_cnt
  2  FROM test_order_order
  3  GROUP BY id;

        ID    SUM_CNT
---------- ----------
         1         10
        22         62
        25         20
         6         40
        11        120
        13        140
         2         40
        14         90
        20         35
        21         65
         4         80

        ID    SUM_CNT
---------- ----------
         5         40
        24         80
         8         40
        17         70
        23         72
         3         30
         7         40
        18         95
         9         40
        10         60
        12        130

        ID    SUM_CNT
---------- ----------
        15         40
        16         40
        19         40

25 rows selected.

SQL> SELECT id, SUM(cnt) sum_cnt
  2  FROM test_order_order
  3   GROUP BY id
  4  ORDER BY Id;

        ID    SUM_CNT
---------- ----------
         1         10
         2         40
         3         30
         4         80
         5         40
         6         40
         7         40
         8         40
         9         40
        10         60
        11        120

        ID    SUM_CNT
---------- ----------
        12        130
        13        140
        14         90
        15         40
        16         40
        17         70
        18         95
        19         40
        20         35
        21         65
        22         62

        ID    SUM_CNT
---------- ----------
        23         72
        24         80
        25         20

25 rows selected.



[Added]
Found the following link:
Change behavior of GROUP BY clause in Oracle 10g
I hope this might be useful for you.

Regards,
Jo

[Updated on: Tue, 17 February 2009 10:40]

Report message to a moderator

Re: Group By Clause with Order By Clause [message #387020 is a reply to message #386373] Tue, 17 February 2009 10:42 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
> I am getting the output in order by using Group By clause without using order by clause. How?
By dumb luck & pure circumstance?
Rows in a table have NO inherent order.
Rows in a table are like balls in basket.
Which ball in a basket is the "first" ball?
Which ball is "next"?
Re: Group By Clause with Order By Clause [message #387065 is a reply to message #387020] Tue, 17 February 2009 16:24 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Quote:
Which ball in a basket is the "first" ball?

The red one of course
Quote:
Which ball is "next"?
That depends on the season and which hemisphere you're in.
Re: Group By Clause with Order By Clause [message #387074 is a reply to message #387018] Tue, 17 February 2009 19:13 Go to previous messageGo to next message
ygsunilkumar
Messages: 100
Registered: August 2008
Senior Member
I read that link, it says that Oracle 10g or later will not sort in the order by using Group By Clause but in Oracle 9i it will allow (will sort in the order). Is that correct?
Re: Group By Clause with Order By Clause [message #387076 is a reply to message #386373] Tue, 17 February 2009 19:24 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
> Is that correct?
It will be correct until it is not correct.
It may be true for heap tables & not true for other object types.
If you want to play Russian Roulette, you don't need anybody's permission. It is your reputation.
Re: Group By Clause with Order By Clause [message #387132 is a reply to message #387074] Wed, 18 February 2009 00:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is an order or there may be several orders and an order does mean the order you're thinking about and these orders may change with patch level and may be combined to undeterministic way and so YOU can't determine a priori if there will be an order and so for YOU there is no order.

Regards
Michel

[Updated on: Wed, 18 February 2009 00:36]

Report message to a moderator

Re: Group By Clause with Order By Clause [message #387142 is a reply to message #387074] Wed, 18 February 2009 00:51 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
I don't understand some people.
You come here to ask a question about the relation between GROUP BY and an ordered resultset.
Multiple people responded that ORDER BY is the only way to be sure resultsets are ordered each and every time. Littlefoot even provided proof.

- The fact that your testrun gave you an ordered resultset does not prove Littlefoot wrong, it displays proof of what we claimed all along: not using ORDER BY gives unpredictable results.
- If you are so eager to prove us wrong, why ask in the first place?

It is like you ask the forum "Is there such a thing as a white elephant, because I never have seen one"
People respond that in the books there are references of white elephants. Then Littlefoot even sends a picture of him standing next to his white elephant.
Your response to that is to send in a picture of your grey elephant, stating that you don't understand why his is white.

[Updated on: Wed, 18 February 2009 00:56]

Report message to a moderator

Re: Group By Clause with Order By Clause [message #387146 is a reply to message #387142] Wed, 18 February 2009 00:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
./fa/449/0/

Regards
Michel
Re: Group By Clause with Order By Clause [message #387150 is a reply to message #387146] Wed, 18 February 2009 01:07 Go to previous messageGo to next message
ygsunilkumar
Messages: 100
Registered: August 2008
Senior Member
I donot understand why people misunderstand the question. I just post my example saying that result is coming in the order without using Order By Clause and asked what is the logic behind it? nothing else. Not to kid you people Mad
Re: Group By Clause with Order By Clause [message #387159 is a reply to message #387150] Wed, 18 February 2009 01:42 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
ygsunilkumar wrote on Wed, 18 February 2009 08:07
I donot understand why people misunderstand the question. I just post my example saying that result is coming in the order without using Order By Clause and asked what is the logic behind it? nothing else. Not to kid you people Mad
Well, you just were lucky. There is no logic behind it. It just appeared to be a sorted result while the results were actually returned in a random order. That is what all people are trying to tell you. No need to get mad or angry.

Bottom line is: GROUP BY doesn't sort, at least not in the way you want it. Prior to 10g, Oracle did sort...kind of. It isn't an alphanumeric or a numeric sort but a binary sort. I wouldn't rely on that. If you want to ORDER, why don't you use the ORDER BY?

A link.

MHE
Re: Group By Clause with Order By Clause [message #387160 is a reply to message #387150] Wed, 18 February 2009 01:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ygsunilkumar wrote on Wed, 18 February 2009 08:07
I donot understand why people misunderstand the question. I just post my example saying that result is coming in the order without using Order By Clause and asked what is the logic behind it? nothing else. Not to kid you people Mad

I do not understand why YOU misunderstand the answerS. Mad

Regards
Michel

[Updated on: Wed, 18 February 2009 01:55]

Report message to a moderator

icon7.gif  Re: Group By Clause with Order By Clause [message #387166 is a reply to message #387159] Wed, 18 February 2009 02:01 Go to previous messageGo to next message
ygsunilkumar
Messages: 100
Registered: August 2008
Senior Member
Thank you very much Maaher. Now i got it.
Re: Group By Clause with Order By Clause [message #387173 is a reply to message #387166] Wed, 18 February 2009 03:00 Go to previous messageGo to next message
m_golam_hossain
Messages: 89
Registered: August 2008
Location: Uttara, Dhaka, Bangladesh
Member

Dear Michel, Maheer, and Frank,

Can we now drop the issue of Group By and Order By, for now!

Regards.
Re: Group By Clause with Order By Clause [message #387193 is a reply to message #387173] Wed, 18 February 2009 04:03 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
m_golam_hossain wrote on Wed, 18 February 2009 10:00
Dear Michel, Maheer, and Frank,

Can we now drop the issue of Group By and Order By, for now!

Regards.


What do you mean? If the original poster has questions, should we not answer??
Re: Group By Clause with Order By Clause [message #387354 is a reply to message #387193] Wed, 18 February 2009 23:37 Go to previous messageGo to next message
m_golam_hossain
Messages: 89
Registered: August 2008
Location: Uttara, Dhaka, Bangladesh
Member

Off course, we must learn and shere ideas.

In the previous postings the words
Quote:

.... UNDERSTAND....MISUNDERSTAND...


soud a little bit harsh.

Is not it good to respect others ideas and tolerate others opnions?

Best Regards to our wise moderators.


[Updated on: Wed, 18 February 2009 23:40]

Report message to a moderator

Re: Group By Clause with Order By Clause [message #387367 is a reply to message #387354] Thu, 19 February 2009 00:43 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Is not it good to respect others ideas and tolerate others opnions?

There is no matter of ideas or opinions, it is matter of facts and in this matter tolerance is irrelevant.

Regards
Michel
Previous Topic: After Update Trigger
Next Topic: Create tab delimited report for excel
Goto Forum:
  


Current Time: Sun Dec 04 10:25:06 CST 2016

Total time taken to generate the page: 0.06314 seconds