Subquery that accesses main table fields combined with LIMIT clause in Oracle SQL

Question

I got a table Users and a table Tasks. Tasks are ordered by importance and are assigned to a user's task list. Tasks have a status: ready or not ready. Now, I want to list all users with their most important task that is also ready.

The interesting requirement that the tasks for each user first need to be filtered and sorted, and then the most important one should be selected. This is what I came up with:

SELECT Users.name,
    (SELECT *
        FROM (SELECT Tasks.description
              FROM Tasks
              WHERE Tasks.taskListCode = Users.taskListCode AND Tasks.isReady
              ORDER BY Tasks.importance DESC)
        WHERE rownum = 1
    ) AS nextTask
    FROM Users

However, this results in the error

ORA-00904: "Users"."taskListCode": invalid identifier

I think the reason is that oracle does not support correlating subqueries with more than one level of depth. However, I need two levels so that I can do the WHERE rownum = 1.

I also tried it without a correlating subquery:

SELECT Users.name, Task.description
FROM Users
LEFT JOIN Tasks nextTask ON
    nextTask.taskListCode = Users.taskListCode AND
    nextTask.importance = MAX(
        SELECT tasks.importance 
        FROM tasks
        WHERE tasks.isReady
        GROUP BY tasks.id
    )

This results in the error

ORA-00934: group function is not allowed here

How would I solve the problem?

Answer 1

Please try with analytic function:

with tp as (select t.*, row_number() over (partition by taskListCode order by importance desc) r 
            from tasks t 
            where isReady = 1 /*or 'Y' or what is positive value here*/)
select u.name, tp.description 
  from users u left outer join tp on (u.taskListCode = tp.taskListCode) 
  where tp.r = 1;

Answer 2

One work-around for this uses keep:

SELECT u.name,
       (SELECT MAX(t.description) KEEP (DENSE_RANK FIRST ORDER BY T.importance DESC)
        FROM Tasks t
        WHERE t.taskListCode = u.taskListCode AND t.isReady
       ) as nextTask
FROM Users u;

Answer 3

Here is a solution that uses aggregation rather than analytic functions. You may want to run this against the analytic functions solution to see which is faster; in many cases aggregate queries are (slightly) faster, but it depends on your data, on index usage, etc.

This solution is similar to what Gordon tried to do. I don't know why he wrote it using a correlated subquery instead of a straight join (and don't know if it will work - I've never seen the FIRST/LAST function used with correlated subqueries like that).

It may not work exactly right if there may be NULL in the importance column - then you will need to add nulls first after t.importance and before ). Note: the max(t.description) is needed, because there may be ties by "importance" (two tasks with the same, highest importance for a given user). In that case, one task must be chosen. If the ordering by importance is strict (no ties), then the MAX() does nothing as it selects the MAX over a set of exactly one value, but the compiler doesn't know that beforehand so it does need the MAX().

select u.name, 
       max(t.description) keep (dense_rank last order by t.importance) as descr
from   users u left outer join tasks t on u.tasklistcode = t.tasklistcode
where  t.isready = 'Y'
group by u.name