CODEWITHSUNDEEP
sqlms-accessms-access-2010
ranopano
ranopano

Reputation: 539

Min() Subquery in Access

I've been tasked to display the MGR and salary of the lowest paid employee for that manager.

I need to exclude anyone where the MGR is unknown and to also exclude any groups where the minimum salary is less than $1000. The results should be sorted in descending order by salary.

Here is the table:

    +-------+--------+-----------+------+------------+-----------+-----------+------+
    | Empno | Ename  |    Job    | Mgr  |  Hiredate  |    Sal    |   Comm    | Dept |
    +-------+--------+-----------+------+------------+-----------+-----------+------+
    |  7839 | KING   | PRESIDENT |      | 11/17/1981 | $5,000.00 | $0.00     |   10 |
    |  7782 | CLARK  | MANAGER   | 7839 | 6/9/1981   | $2,450.00 | $0.00     |   10 |
    |  7934 | MILLER | CLERK     | 7782 | 1/23/1982  | $1,300.00 | $0.00     |   10 |
    |  7902 | FORD   | ANALYST   | 7566 | 12/3/1981  | $3,000.00 | $0.00     |   20 |
    |  7788 | SCOTT  | ANALYST   | 7566 | 12/9/1982  | $3,000.00 | $0.00     |   20 |
    |  7876 | ADAMS  | CLERK     | 7788 | 1/12/1983  | $1,100.00 | $0.00     |   20 |
    |  7369 | SMITH  | CLERK     | 7902 | 12/17/1980 | $800.00   | $0.00     |   20 |
    |  7566 | JONES  | MANAGER   | 7839 | 4/2/1981   | $0.00     | $0.00     |   20 |
    |  7698 | BLAKE  | MANAGER   | 7839 | 5/1/1981   | $2,850.00 | $0.00     |   30 |
    |  7499 | ALLEN  | SALESMAN  | 7698 | 2/20/1981  | $1,600.00 | $300.00   |   30 |
    |  7844 | TURNER | SALESMAN  | 7698 | 9/8/1981   | $1,500.00 | $0.00     |   30 |
    |  7521 | WARD   | SALESMAN  | 7698 | 2/22/1981  | $1,250.00 | $500.00   |   30 |
    |  7654 | MARTIN | SALESMAN  | 7698 | 9/28/1981  | $1,250.00 | $1,400.00 |   30 |
    |  7900 | JAMES  | CLERK     | 7698 | 12/3/1981  | $950.00   | $0.00     |   30 |
    +-------+--------+-----------+------+------------+-----------+-----------+------+

Here is my code so far:

SELECT EMp.Mgr, EMp.Ename, EMp.Sal AS Sal
FROM EMp
GROUP BY EMp.Mgr, EMp.Ename, EMp.Sal
HAVING (((EMp.Mgr) Is Not Null) AND ((EMp.Sal)>1000))
ORDER BY EMp.Sal DESC;

The problem with my current code is that it is not taking into consideration the minimum salary parameter. I believe this needs to be done by using a subquery, though I'm entirely sure how to proceed with that...

Can anyone please assist?

Upvotes: 0

Views: 65

Answers (3)

Steve Lovell
Steve Lovell

Reputation: 2564

The following is closely based on Jerrad's answer, but addresses the concern about that which I raised in a comment there:

SELECT Emp.Mgr, Emp.Ename, Emp.Sal AS Sal
FROM Emp
WHERE Emp.Sal=(SELECT MIN(sal)
               FROM Emp as Emp2
               WHERE Emp2.MGr = Emp.Mgr
               HAVING min(Emp2.sal) >= 1000)
GROUP BY Emp.Mgr, Emp.Ename, Emp.Sal
HAVING Emp.Mgr Is Not Null
ORDER BY Emp.Sal DESC;

Using your sample data, returns the same rows as Jerrad's except for omitting those with manager 7839 or 7698. Amongst others, these two manage staff with salaries of $0 and $950. The way I was interpreting the original question ("exclude any groups where the minimum salary is less than $1000"), these managers should be excluded from the results.

Upvotes: 0

Jerrad
Jerrad

Reputation: 5290

Try this:

SELECT EMp.Mgr, EMp.Ename, EMp.Sal AS Sal
FROM EMp
WHERE emp.Sal = (select MIN(sal) from emp as emp2 where emp2.MGr = emp.Mgr and emp2.sal > 1000)
GROUP BY EMp.Mgr, EMp.Ename, EMp.Sal
HAVING EMp.Mgr Is Not Null
ORDER BY EMp.Sal DESC;

Upvotes: 1

Srinivasa Rao
Srinivasa Rao

Reputation: 172

Please try

with one as 
(
SELECT EMp.Mgr,min(EMp.Sal) MinSlary
FROM EMp
GROUP BY EMp.Mgr
)
select a.Mgr,b.EName,b.Sal from one a
inner join Emp b on a.Mgr=b.Mgr and a.MinSlary=b.Sal
where a.Mgr is not null and a.MinSlary>1000

Upvotes: 0

Related Questions