CODEWITHSUNDEEP
sqloracle-databasegreatest-n-per-group
bob
bob

Reputation: 19

SQL help needed (oracle application express)

Given this information

Among all projects chen work on, list the name of project that has lowest budget.

EMPID   NAME    SALARY  DID
---------------------------
1       kevin   32000   2
2       joan    42000   1
3       brian   37000   3
4       larry   82000   5
5       harry   92000   4
6       peter   45000   2
7       peter   68000   3
8       smith   39000   4
9       chen    71000   1
10      kim     46000   5
11      smith   46000   1

PID EMPID   HOURS
-----------------
3   1       30
2   3       40
5   4       30
6   6       60
4   3       70
2   4       45
5   3       90
3   3       100
6   8       30
4   4       30
5   8       30
6   7       30
6   9       40
5   9       50
4   6       45
2   7       30
2   8       30
2   9       30
1   9       30
1   8       30
1   7       30
1   5       30
1   6       30
2   6       30

PID PNAME              BUDGET       DID
---------------------------------------
1   DB development      8000        2
2   network development 6000        2
3   Web development      5000       3
4   Wireless development 5000       1
5   security system      6000       4
6   system development   7000       1

This is what I've written so far:

select min(budget), pname
from employee e, workon w, project p
where e.empid = w.empid and p.pid = w.pid and name = 'chen'
group by pname

Which gives me 

MIN(BUDGET) PNAME
--------------------------
8000        DB development
6000        security system
6000        network development
7000        system development

How can I select just the lowest (the minimum budget) from these values? Thanks for any help.

Upvotes: 0

Views: 83

Answers (2)

Donal
Donal

Reputation: 32713

select budgets.budget, budgets.pname
from
(
    select min(budget) as budget, pname
    from employee e, workon w, project p
    where e.empid = w.empid and p.pid = w.pid and name = 'chen'
    group by pname
    order by budget asc
) budgets     
where rownum = 1

Upvotes: 1

radar
radar

Reputation: 13425

use explicit join syntax,

Here is one way using analytic function row_number, sequence number given based on budget with lowest being the first.

with cte
as
(
select row_number() over (  order by budget asc) as rn, pname, budget
from employee e
join workon w
on e.empid = w.empid
join project p
on p.pid = w.pid and name = 'chen'
)
select pname, budget from cte where rn =1

Upvotes: 1

Related Questions