CODEWITHSUNDEEP
sqloracle-database
Radha Gogia
Radha Gogia

Reputation: 795

why is aggregate function not working with Inline view for below query?

I have 2 tables emp and salgrade whose schema is defined as : emp(empno,ename,sal,mgr) and salgrade(grade,losal,hisal)

Query is

List the most senior empl working under the king and grade is more than 3.

I have written it as stated below by first extracting all the employees working under KING with grade >3 and then from it extracting the senior most employee along with its hiredate but it is giving me syntax error ,not a single-group-group function ,please guide where am I doing wrong ? 

SELECT ename, 
       Min(hiredate) 
FROM   (SELECT ename, 
               hiredate 
        FROM   emp, 
               salgrade 
        WHERE  mgr = (SELECT empno 
                      FROM   emp 
                      WHERE  ename = 'KING') 
               AND salgrade.grade > 3 
               AND emp.sal BETWEEN salgrade.losal AND salgrade.hisal);

Upvotes: 0

Views: 82

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93724

Add Group by to find the minimum Hiredate for each ename 

SELECT ename, 
       Min(hiredate) 
FROM   (SELECT ename, 
               hiredate 
        FROM   emp, 
               salgrade 
        WHERE  mgr = (SELECT empno 
                      FROM   emp 
                      WHERE  ename = 'KING') 
               AND salgrade.grade > 3 
               AND emp.sal BETWEEN salgrade.losal AND salgrade.hisal) 
GROUP  BY ename  --Here

The query can simplified like this

SELECT ename, 
       Min(hiredate) 
FROM   emp 
       JOIN salgrade 
         ON mgr = empno 
            AND emp.sal BETWEEN salgrade.losal AND salgrade.hisal 
WHERE  ename = 'KING' 
       AND salgrade.grade > 3 
GROUP  BY ename

Update : To find the senior most employee Use Order by and ROWNUM

SELECT * 
FROM   (SELECT * 
        FROM   emp 
               join salgrade 
                 ON mgr = empno 
                    AND emp.sal BETWEEN salgrade.losal AND salgrade.hisal 
        WHERE  ename = 'KING' 
               AND salgrade.grade > 3 
        ORDER  BY hiredate ASC) 
WHERE  ROWNUM = 1

Upvotes: 2

Related Questions