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?

What do you mean by `WHERE task.isready`? There are no Boolean values in Oracle database. — , Dec 22 '16 at 14:57

score 2 · Answer 1 · answered Dec 22 '16 at 12:38

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;

answered Dec 22 '16 at 12:38

Gordon Linoff

1,242,037
58
646
786

This `MAX(t.description)` looks strange, or is it required for the work-around? Otherwise, this looks more concise than Kacper's answer. Do you know whether the performance is different? – Yogu Dec 22 '16 at 13:52
@Yogu . . . In fact, it does look strange. The `MAX()` does nothing because `DENSE_RANK FIRST` matches only one value . . .so `MIN()` and `MAX()` would do the same thing. I've never used `KEEP` in a context where values from multiple rows might be returned; but I presume that the aggregation function probably serves a use under some circumstance. – Gordon Linoff Dec 22 '16 at 16:13
@GordonLinoff - "dense_rank first" may run into ties, if ordering by "importance" is not strict. There may be three tasks with the same, highest importance. Then MAX will pick the task with the latest description in alphabetical order. MIN will pick the first description by alphabetical order - from among the tasks with the highest importance. – Dec 23 '16 at 03:19

score 1 · Accepted Answer · answered Dec 22 '16 at 11:42

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;

score 1 · Answer 3 · answered Dec 22 '16 at 15:07

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

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

3 Answers3