CODEWITHSUNDEEP
sqloracle-databasenestedhavingclause
doublelift2
doublelift2

Reputation: 15

Group by, Subquery and Nested group by function in the Having clause

Trying to display the details of a staff member with the most amount of orders, in oracle there is an error stating that the group function is 'nested too deeply'.

SELECT c.Staff_No, s.First_name, s.Last_Name, max(COUNT(*)) AS "Number Of Orders"
  FROM Cust_Order c, 
       Staff s   
 WHERE c.Staff_No = s.Staff_No
 GROUP 
    BY c.Staff_No, s.First_name, s.Last_Name
HAVING MAX(COUNT(*)) > (SELECT MAX((COUNT(*))
                          FROM Cust_Order c, Staff s 
                         WHERE c.Staff_No = s.Staff_No);

Upvotes: 1

Views: 1108

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270773

Instead, use analytic functions:

SELECT Staff_No, First_name, Last_Name, "Number Of Orders"
FROM (SELECT c.Staff_No, s.First_name, s.Last_Name, COUNT(*) AS "Number Of Orders",
             max(count(*)) over () as maxcount
      FROM Cust_Order c JOIN
           Staff s                                                                                                                                                                                                                                                                                                                          
           ON c.Staff_No = s.Staff_No
      GROUP BY c.Staff_No, s.First_name, s.Last_Name
     ) cs
WHERE "Number Of Orders" = maxcount;

This should also have better performance.

Upvotes: 1

Related Questions