Home » SQL & PL/SQL » SQL & PL/SQL » Effect of group by? (Oracle 10g)
Effect of group by? [message #390170] Thu, 05 March 2009 05:34 Go to next message
mbmalasenthil
Messages: 27
Registered: July 2008
Junior Member
Hi, I have two queries generated on emp table.
I have attached the results below:

select empno, ename, job from emp

1 7369 SMITH CLERK
2 7499 ALLEN SALESMAN
3 7521 WARD SALESMAN
4 7566 JONES MANAGER
5 7654 MARTIN SALESMAN
6 7698 BLAKE MANAGER
7 7782 CLARK MANAGER
8 7788 SCOTT ANALYST
9 7839 KING PRESIDENT
10 7844 TURNER SALESMAN
11 7876 ADAMS CLERK
12 7900 JAMES CLERK
13 7902 FORD ANALYST
14 7934 MILLER CLERK



select empno, ename, job from emp group by empno, ename, job

1 7521 WARD SALESMAN
2 7839 KING PRESIDENT
3 7934 MILLER CLERK
4 7499 ALLEN SALESMAN
5 7782 CLARK MANAGER
6 7788 SCOTT ANALYST
7 7844 TURNER SALESMAN
8 7876 ADAMS CLERK
9 7369 SMITH CLERK
10 7900 JAMES CLERK
11 7566 JONES MANAGER
12 7698 BLAKE MANAGER
13 7654 MARTIN SALESMAN
14 7902 FORD ANALYST

I would like to know what exactly has happened in the second query for the result to be like this..? Meaning, the steps involved in arriving at the result!
Thanks,
B Senthil
Re: Effect of group by? [message #390171 is a reply to message #390170] Thu, 05 March 2009 05:37 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
What do you expect us to tell you that can't be found in the documentation?

What do you think is the issue here?
Re: Effect of group by? [message #390173 is a reply to message #390171] Thu, 05 March 2009 05:42 Go to previous messageGo to next message
mbmalasenthil
Messages: 27
Registered: July 2008
Junior Member
I would like to know why is the sorting/order of records the way it appears in the second resultset. If i have aggregate functions used on the query i get the real use of group. Here with no such functions being used and it being a blind group by on the columns I dont understand how the group by has arrived at the result. Sorry if my question looks silly.
Thanks!
Re: Effect of group by? [message #390174 is a reply to message #390170] Thu, 05 March 2009 05:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Meaning, the steps involved in arriving at the result!

Try to execute an explain plan.

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel
Re: Effect of group by? [message #390176 is a reply to message #390173] Thu, 05 March 2009 05:46 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The database has gone through your data, grouped it by the 3 columns you've specified, and output a row for each unique combination of values.

The order of the data is completely irrelevant - if you don't have an Order By clause, then there is by definition no correct order to the rows.

What, in detail, is it that you don't understand about the data in the second query?
Previous Topic: Calling unix command from pl/sql
Next Topic: Dependencies of procedures within a stored package (merged)
Goto Forum:
  


Current Time: Fri Dec 02 23:22:26 CST 2016

Total time taken to generate the page: 0.10434 seconds