Home » SQL & PL/SQL » SQL & PL/SQL » group by versus distinct
group by versus distinct [message #189641] Fri, 25 August 2006 06:55 Go to next message
punuIT
Messages: 1
Registered: August 2006
Junior Member
Hi,

I just started on oracle.I need some clarification between "group by versus distinct" clause.

I know their respective meaning & usage but following two different query are confusing me

1. select distinct deptno,job from emp;

DEPTNO JOB
---------- ---------
10 CLERK
10 MANAGER
10 PRESIDENT
20 ANALYST
20 CLERK
20 MANAGER
30 CLERK
30 MANAGER
30 SALESMAN

9 rows selected.

2. select deptno,job from emp
group by deptno,job;

DEPTNO JOB
---------- ---------
10 CLERK
10 MANAGER
10 PRESIDENT
20 CLERK
20 ANALYST
20 MANAGER
30 CLERK
30 MANAGER
30 SALESMAN

The result differ only in the ordering. Now my question is

1. whats is the reason for the above similar result?
2. What is the differnece between above two result ( may be I am missing something here)??
3. Is there similarity between group by & distinct?
4. Keeping above query & result in mind, In which scenario we should use group by & distinct???

All forum gurus please enlighten me. Any advice or comment are most welcome.

regards
Re: group by versus distinct [message #189646 is a reply to message #189641] Fri, 25 August 2006 07:21 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
For the query you are doing, there's no real difference between the two.
The result sets are the same, because the questions you have asked are the same - get me the set of distinct values of DEPTNO and JOB from this table.

DISTINCT will perform a sort on the result set and extract the unique values, whereas GROUP BY will perform a sort on the result set, extract the unique values and perform any required aggregation functions.

In general, you would use DISTINCT to get a distinct set of values as the syntax is easier, and GROUP BY to perform aggregation operations (SUM, COUNT, AVG etc)
Re: group by versus distinct [message #189648 is a reply to message #189646] Fri, 25 August 2006 07:30 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Just a small addition: do NOT use group by to perform an ordering. It might order, it might not.
To get an ordered resultset use order by.
Re: group by versus distinct [message #189655 is a reply to message #189648] Fri, 25 August 2006 07:57 Go to previous message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
And to add to Frank's answer, it seemed that most of the times GROUP BY did in fact return results ordered by the columns in the GROUP BY in versions prior to 10g (even though Oracle kept insisting NOT to rely on a GROUP BY for ordering). In version 10g, it almost never return the results properly ordered (and it shouldn't!). I always wondered if Oracle did that on purpose Wink

[Updated on: Fri, 25 August 2006 07:57]

Report message to a moderator

Previous Topic: UTL_FILE - Invalid Directory or path
Next Topic: Language Text Conversion to UTF8 -
Goto Forum:
  


Current Time: Sun Dec 04 19:04:59 CST 2016

Total time taken to generate the page: 0.11542 seconds