How to write this SQL query as per requirement

Question

Consider the following schema of a company database (primary keys are in bold).

Employee (**EmployeeID**, Name, Office, Email, Sex, Salary, DepartmentID)
Department (**DepartmentID**, DepartmentName)
Project (**ProjectID**, DepartmentID, Duration)
EmployeeProject (**EmployeeID, ProjectID**, WorkHours)

Find the proejct whose total work hours is the second longest, with SINGLE QUERY statement. Try not use top or limit.

Below is my solution, its rather ugly and inefficient.

select projectid
from (
    select projectid, sum(workhours) as hours
    from employeeproject ep
    group by projectid
) sum
where hours = (
    select max(hours)
    from (
        select projectid, hours
        from (
            select projectid, sum(workhours) as hours
            from employeeproject ep
            group by projectid
        ) sum
        where hours < (
            select max(hours)
            from (
                select projectid, sum(workhours) as hours
                from employeeproject ep
                group by projectid
            ) sum
        )
    ) sec
)

It seems to be like some assignment or interview question... — Krishnraj Rana, Aug 21 '14 at 16:50
Ok to ask...ok with psuedo answer? I try to avoid the where in () logic and prefer joining subqueries instead. Create a max query that gets the sum of hours by projectID...left join this to a subquery that selects the maximum hours value. Have a where clause to filter out where the subquery is null (effectively filters out the longest project)...select max from that to get second longest. It's a few nested subqueries, but I'd go that route before the where in / where not in route — Twelfth, Aug 21 '14 at 17:29
select sum3.projectid from ( select projectid, sum(workhours) as hours from employeeproject ep group by projectid ) sum3 where hours = ( select max(hours) from ( select sum1.projectid, sum1.hours from ( select projectid, sum(workhours) as hours from employeeproject ep group by projectid ) sum1, ( select projectid, sum(workhours) as hours from employeeproject ep group by projectid ) sum2 where sum1.hours < sum2.hours ) sec ) — David, Aug 21 '14 at 17:54

score 0 · Accepted Answer · edited Mar 20 '17 at 10:29

0

Since this appears to be a homework question, full answers are out as per the site policy: https://meta.stackexchange.com/a/10812

However, it looks like we are allowed to point you in the right direction. :)

Try breaking the problem down into steps:

Find the total work hours for each project - you've already done that.
Find the two longest projects - look at the TOP and ORDER BY operators.
Find the shortest of those two projects - ORDER BY can work in ascending or descending order.

It should then be fairly obvious how to combine those steps into a single query.

edited Mar 20 '17 at 10:29

Community

1
1

answered Aug 21 '14 at 17:31

Richard Deeming

29,830
10
79
151

select projectid from ( select projectid, sum(workhours) as hours from employeeproject ep group by projectid order by sum(workhours) desc limit 2 ) sum order by hours limit 1 – David Aug 21 '14 at 17:58
I guess this is what you mean, it works with my test data. Somehow top or limit is not taught at all, so I think that is not the expecting answer. – David Aug 21 '14 at 18:01
@David: I guess it depends on whether your teacher will only accept a specific answer, or will accept any answer that solves the problem as stated. :) – Richard Deeming Aug 21 '14 at 18:03
Yes, I think I need to clarify this accordingly. Thanks. – David Aug 21 '14 at 18:14

score 0 · Answer 2 · answered Dec 06 '19 at 11:58

select ProjectID from EmployeeProject where WORKHOURS = (select MAX(WORKHOURS) FROM EmployeeProject WHERE WORKHOURS < ( SELECT MAX(WORKHOURS) FROM EmployeeProject ) )

How to write this SQL query as per requirement

2 Answers2