CODEWITHSUNDEEP
sqloracle-databasegroup-bysql-order-by
Yekatjarina Aljaksandrayna Shu
Yekatjarina Aljaksandrayna Shu

Reputation: 29

`ORDER BY` changes the query result even if column being sorted is not in `SELECT` statement

I was working through some Oracle PL/SQL videos and I arrived at a part where a discussed query did not quite make sense to me. Particularly, how the result of the ORDER BY clause play into the resulting query.

Looking at this tables structure:

Category

Show

And a 0 to 1 or many relationship between the Category and Show tables (so that for any category there may be 0 or more records in the Show table).

How does the ORDER BY clause modify what is achieved by the GROUP BY clause if the COUNT (DISTINCT s.show_id) is not a listed column/field within the SELECT statement

Query:

SELECT c.name, COUNT(DISTINCT s.category_ID) "First Categories"
FROM category c, show s
WHERE c.category_id = s.category_id
GROUP BY c.name
ORDER BY COUNT (DISTINCT s.show_id);

(This is in response to the down-vote: I would like to address this in that I have searched for the answer on the boards prior to posting extensively, as well as outside. I felt that this example covers a good amount of basic and important concepts between GROUP BY, SELECT and ORDER BY clauses and might be helpful for someone else who is just learning, as it is a unique enough example, but basic enough to grasp and apply down the road.)

Thank you graciously!!

Upvotes: 1

Views: 958

Answers (1)

Mureinik
Mureinik

Reputation: 312086

The calculation of COUNT (DISTINCT s.show_id) is still applied to each group of c.name, and the resulting rows are sorted according to it - it just isn't displayed in the final result set.

Upvotes: 0

Related Questions