| question based on Oracle PL/SQL Programming book code [message #390019] |
Wed, 04 March 2009 11:03  |
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 
|
|
|
|
| Re: question based on Oracle PL/SQL Programming book code [message #390023 is a reply to message #390019] |
Wed, 04 March 2009 11:28  |
cookiemonster
Messages: 13920
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.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
