How to select the row with the max column from another subquery in Oracle

Basically I’m trying to find the max of a count outcome. Here’s an example:

Desired output: the name of the employees with the highest number of jobs and the number of jobs done.

My tables (bold means primary key, * means foreign key):

• Employees (Id, employee_name,….)

• Sites (Site_id, Site_name,….)

• Jobs (Site_id*, Id*, DateJ)

Here are 2 things I tried:

Query #1:

Select 
    employee_name, 
    max(jobs_done) max_jobs
from 
    Employees E,
    (select 
         id, 
         count(*) jobs_done from jobs
     from jobs
     group by id) t
where 
    E.id = t.Id
group by 
    employee_name;

This returns the max jobs of each employee which is pointless as the subquery already does that, and it’s not the desired outcome.

Query #2:

Select 
    employee_name,
    t.job_done
from
    Employees E
    (Select 
         id, 
         count(*) job_done
     from Jobs
     group by id) t
where 
    E.id = t.id
order by 
    jobs_done desc
fetch first row only;

This kinda works in my situation, but doesn’t account to multiple employees having the same max.

is there a simple way to solve this, obviously without changing the DB layout and preferably using only subqueries (I’m still a beginner)?

Answer

First of all, looks like you need to aggregate by Employee.ID, instead of Employee_name, since jobs are linked by ID, not employee_name. So it’s better to push aggregating and Top-N into the subquery to Jobs. Though Oracle has a special query transformation mechanism Group-By Pushdown (and even a special hint gby_pushdown), it’s better to make own queries clearer and more predictable and do not rely on CBO (Oracle Optimizer) magic. Secondly, I would suggest to use ANSI join syntax at least for beginners, since it’s a bit more clear.

So it should look like this:

Select 
    e.id,
    e.employee_name,
    j.jobs_done
from
    (Select 
         id, 
         count(*) jobs_done
     from Jobs
     group by id
     order by jobs_done desc
     fetch first 1 row with ties
    ) j
    join Employees e
        on e.id = j.id
;

As you can see j subquery aggregates jobs by employee.id and gets only top 1 with ties, then we just join Employee to get Employee_name