OraFAQ Forum: SQL & PL/SQL » Group By clause in inline view

Home » SQL & PL/SQL » SQL & PL/SQL » Group By clause in inline view (Oracle Database 10g, Toad 9.7, Windows 7)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Group By clause in inline view [message #650173]

Mon, 18 April 2016 10:05

lancer26
Messages: 52
Registered: May 2006
Location: Pakistan

Member

Dear Gurus,
Please check below query and explain why it's working, as my understanding, it should not work, because of inline view, ID column is not part of Group by clause.
if i run only inner query, it gives me error "not a group by expression", but when we use it as inline view and then select aggregate then it'll work.

Query is below

WITH CTE
AS
(
SELECT 1 ID, 'A' D FROM DUAL
UNION
SELECT 2 ID, 'B' FROM DUAL
UNION
SELECT 3 ID, 'C' FROM DUAL
)
SELECT MAX_ID
FROM (
SELECT ID, MAX(ID) MAX_ID
FROM CTE
GROUP BY D
)
;

Report message to a moderator

Re: Group By clause in inline view [message #650174 is a reply to message #650173]

Mon, 18 April 2016 10:54

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

It shouldn't work, and doesn't in my 11.2.0.3.0 instance.
That's a bug in your version.

Report message to a moderator

Re: Group By clause in inline view [message #650179 is a reply to message #650173]

Mon, 18 April 2016 12:55

Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

It's a known bug I reported in 10g database.
The optimizer thinks it is smarter than you and allows itself to fix the GROUP BY for you. Sad

It is still present in 10.2.0.4.

[Updated on: Mon, 18 April 2016 12:55]

Report message to a moderator

Re: Group By clause in inline view [message #650183 is a reply to message #650179]

Mon, 18 April 2016 14:47

live4learn
Messages: 41
Registered: September 2013
Location: Bangalore, India

Member

Learnt something new !!
Worked in 11.2.0.1.0 too.

SQL> select * from V$version
  2  /
 
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE	11.2.0.1.0	Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
 
SQL> 
SQL> WITH CTE
  2  AS
  3  (
  4  SELECT 1 ID, 'A' D FROM DUAL
  5  UNION
  6  SELECT 2 ID, 'B' FROM DUAL
  7  UNION
  8  SELECT 3 ID, 'C' FROM DUAL
  9  )
 10  SELECT MAX_ID
 11  FROM (
 12  SELECT ID, MAX(ID) MAX_ID
 13  FROM CTE
 14  GROUP BY D
 15  )
 16  ;
 
    MAX_ID
----------
         1
         2
         3
 
SQL>

Report message to a moderator