Query insisting on columns in the 'Order By' clause match columns in the 'Group by' clause

Question

A bit of an odd one this one. I am trying to run the following query joining 3 tables.

Select t3.id, t3.name, t3.phone_no, t1.reg_dtm, count(t1.reg_dtm)
from tableA t1, tableB t2, tableC t3
Where t1.id = t2.id
And t2.id = t3.id
Group by t3.id, t3.name, t3.phone_no, t1.reg_dtm
Order by t2.id, t1.reg_dtm

The above query returns the following error

ORA-00979: not a GROUP BY expression

But if i change it so that everything in the group by clause is in the order by clause then it works.

Select t3.id, t3.name, t3.phone_no, t1.reg_dtm, count(t1.reg_dtm)
from tableA t1, tableB t2, tableC t3
Where t1.id = t2.id
And t2.id = t3.id
Group by t3.id, t3.name, t3.phone_no, t1.reg_dtm
Order by t3.id, t3.name, t3.phone_no, t1.reg_dtm

What exactly is the reason for this?

I think the problem is possibly because t2.id shown in the order by statement on the first query is not part of the group by statement. If this is the cause then why does it matter? I have never experienced this before and didn't think that there was any relationship between the group by and the order by statements.

I tested the above on Oracle 10G as well as MySQL.

Thanks in advance

Answer 1

The ORDER BY clause runs after everything else in the SELECT statement executes; in a GROUPing scenario, the result set is limited to the columns used to aggregate the data. If you don't have a column specified in your initial resultset, the processing engine doesn't understand what to do with the requested output.

In other words, since your query doesn't return distinct values for t2.id and t1.id (since they're not used in the GROUP BY clause), the engine can't return the data in that order.