Home » Other » Training & Certification » question based on Oracle PL/SQL Programming book code
question based on Oracle PL/SQL Programming book code [message #390019] Wed, 04 March 2009 11:03 Go to next message
happyjoshua777
Messages: 33
Registered: February 2009
Location: United States
Member
I have encountered the following code:

SELECT DISTINCT s.course_no
 FROM section s, enrollment e
 WHERE s.section_id = e.section_id
 GROUP BY s.course_no, e.section_id, s.section_id
 HAVING COUNT(*) >= 8;


Now I have read that GROUP BY returns unique results for the COUNT(*). Then what is the point of Distinct in SELECT?
I removed DISTINCT and the query returned exactly the same results, just in different order. Why did that happen?

thank you Smile
Re: question based on Oracle PL/SQL Programming book code [message #390023 is a reply to message #390019] Wed, 04 March 2009 11:28 Go to previous messageGo to next message
cookiemonster
Messages: 12410
Registered: September 2008
Location: Rainy Manchester
Senior Member
With enough data that query will return duplicate course_no's without the distinct.

Group by gives one row per distinct combination of ALL the columns you're grouping by.

The query doesn't display all the columns you're grouping by.

So if you have more than one section_id for a given course_no, you'll see that course_no twice (or more).

The having clause is probably obscuring this.

Try running these:

SELECT s.course_no, e.section_id, s.section_id, COUNT(*)
FROM section s, enrollment e
WHERE s.section_id = e.section_id
GROUP BY s.course_no, e.section_id, s.section_id;

SELECT s.course_no
FROM section s, enrollment e
WHERE s.section_id = e.section_id
GROUP BY s.course_no, e.section_id, s.section_id;


As for the order. Without an ORDER BY clause oracle can return rows in an order it feels like, strictly speaking it's a result of the plan it uses to get the data. Adding or removing a distinct clause is pretty much garanteed to change the plan so a change of order becomes possible.


Finally - what is this book you're using? The examples seem rather odd.
Re: question based on Oracle PL/SQL Programming book code [message #390033 is a reply to message #390023] Wed, 04 March 2009 12:04 Go to previous message
happyjoshua777
Messages: 33
Registered: February 2009
Location: United States
Member
wow! thank you very much for such a detailed well thought out explanation. Smile

the book is called Oracle PL/SQL by Example
3rd edition

Previous Topic: Looking for oracle apps 11i - financial module training in mumbai
Next Topic: some of instructions
Goto Forum:
  


Current Time: Wed Dec 07 04:57:27 CST 2016

Total time taken to generate the page: 0.09012 seconds