CODEWITHSUNDEEP
sqloracle-databasegreatest-n-per-group
user3395936
user3395936

Reputation: 681

Find min(sal) along with employee name SQL Oracle

I have table called emp and I a trying to find the lowest paid Clerk. My code select min(sal) as min from emp where job='CLERK'; works fine and i get this:

  MIN
----------
       800

but I also want to show the name of the clerk which is Smith. When I run this code select ename, min(sal) as min from emp where job='CLERK' group by name; it gives me all the Clerks in the table, which is not really want I want. Here is a snippet of my table:

CREATE TABLE EMP
       (EMPNO NUMBER(4) NOT NULL,
        ENAME VARCHAR2(10),
        JOB VARCHAR2(9),
        MGR NUMBER(4),
        HIREDATE DATE,
        SAL NUMBER(7, 2),
        COMM NUMBER(7, 2),
        DEPTNO NUMBER(2));
INSERT INTO EMP VALUES
        (7369, 'SMITH',  'CLERK',     7902,
        TO_DATE('17-DEC-1980', 'DD-MON-YYYY'),  800, NULL, 20);
INSERT INTO EMP VALUES
        (7499, 'ALLEN',  'SALESMAN',  7698,
        TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600,  300, 30);

Upvotes: 0

Views: 75

Answers (3)

kedar kamthe
kedar kamthe

Reputation: 8178

try this 

SELECT * FROM emp 
WHERE SAL = (select MIN(SAL) sal from emp WHERE JOB ='CLERK')
and JOB ='CLERK';

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269593

You can do this with a subquery and ranking functions:

select e.*
from (select e.*,
             dense_rank() over (partition by job order by salary) as seqnum
      from emp
      where job = 'CLERK' 
     ) e
where seqnum = 1;

This will return multiple rows if there are ties. If you only want one, you can use row_number() instead of dense_rank().

And, if you want the names of all clerks as a single delimited value, then you can use list_agg():

select listagg(e.name, ', ') within group (order by e.name) as names
from (select e.*,
             dense_rank() over (partition by job order by salary) as seqnum
      from emp
      where job = 'CLERK' 
     ) e
where seqnum = 1;

Upvotes: 0

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

You can use row_number:

select ename, sal as min
from (
  select ename, sal,
         row_number() over (order by sal) as rn  
  from emp 
  where job='CLERK' ) t
where t.rn = 1

Upvotes: 0

Related Questions