sql

[kris123]

Hi,

I got the following rows on my emp table...

name job sal hiredate
---- ---- ------ --------

john analyst 2000 10-Jan-02
robert analyst 2500 21-Dec-01
angel prog1 1500 05-Oct-02
ramon prog1 1600 01-Aug-01


I want to display the lowest paid person
per job.

select * from emp where (job,sal) in
(select job,min(sal) from emp group by job);

Is there other way to do it?...

Thanks,

[hrishy]

Hi

Try using inline views


select e1.*
from emp e1,
(select job,min(sal) minsal
from emp
group by job) e2
where e2.minsal=e1.sal


Also index your salary column..:-)


select e1.*
from emp e1 , (select job,sal,rank()
over ( partition by job
order by sal ) rank
from emp ) e2
where e2.rank=1
and
e1.sal=e2.sal

Using analytic functions ..:-)



regards
Hrishy

[slimdave]

select * from 
(select name,job,sal,hiredate,
min(sal) over (partition by job) min_sal
from emp)
where sal = min_sal