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;