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  |
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  |
Frank
Messages: 7901
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 #386455 is a reply to message #386373] |
Sun, 15 February 2009 03:25 |
 |
Littlefoot
Messages: 21823
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 30SQL> 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 |
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 |
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 #387018 is a reply to message #387003] |
Tue, 17 February 2009 10:35 |
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 #387132 is a reply to message #387074] |
Wed, 18 February 2009 00:31 |
 |
Michel Cadot
Messages: 68722
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 |
Frank
Messages: 7901
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 #387193 is a reply to message #387173] |
Wed, 18 February 2009 04:03 |
Frank
Messages: 7901
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 #387367 is a reply to message #387354] |
Thu, 19 February 2009 00:43 |
|
Michel Cadot
Messages: 68722
Registered: March 2007
Location: Saint-Maur, France, https...
|
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
|
|
|
|
Goto Forum:
Current Time: Mon Dec 09 20:53:32 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
