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](https://stackoverflow.com/questions/2048244/oracle-subquery-does-not-see-the-variable-from-the-outer-block-2-levels-up#). 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?