sql store nested select subquery resultset into variable

Question

I am trying to get next/previous record from a result-set that is an output of inner select query.

Here's my query:

select qs.*
from
(
    select
       tsk.id,
       tsk.request_id,
       tsk.category_group,
       tsk.category,
       tsk.hash_id 
    from
       user_tasks as usr 
       inner join
          unassigned_tasks as tsk 
          on usr.task_id = tsk.id 
    where
       usr.assigned_to = 12        
    AND 
        BINARY hash_id NOT IN ( SELECT hash_id FROM product_match_unmatches WHERE request_id = tsk.request_id AND auto_unmatched_by IS NOT NULL )
) as qs

WHERE qs.id = ( SELECT min(qs.id) FROM qs WHERE qs.id > 5181 )

If the inner query returns result set with values as:

id
------
4179
4280
5181
6182
6283

Then I need to select row with id 6182.

But it looks like I have to mention the inner query again inside outer where query.

Is there any other way?

I don't find the relevance of this sub-query. SELECT min(qs.id) FROM qs WHERE qs.id > 5181 As this is an id and it is mostly incremented by one, the next value will obviously be 5182. — Peter Gerald, Nov 07 '19 at 06:04
the output is the result-set of inner query and so they may not be in incremental by one sequence. And the `id 5181` will be passed from the request and is variable. From the output resultset, I am trying to navigate. — Azima, Nov 07 '19 at 06:12

score 1 · Answer 1 · 2019-11-07T06:52:20.467

If you want to select the 2nd to the last value of your inner query you can use row numbering using session variable: (the numbering depends on the row order of your given inner query)

set @rank = 0;
select
    tsk.id,
    tsk.request_id,
    tsk.category_group,
    tsk.category,
    tsk.hash_id
from (
        select
               tsk.id,
               tsk.request_id,
               tsk.category_group,
               tsk.category,
               tsk.hash_id,
               @rank := @rank + 1 as rank
            from
               user_tasks as usr 
               inner join
                  unassigned_tasks as tsk 
                  on usr.task_id = tsk.id 
            where
               usr.assigned_to = 12        
            AND 
                BINARY hash_id NOT IN ( SELECT hash_id FROM product_match_unmatches WHERE request_id = tsk.request_id AND auto_unmatched_by IS NOT NULL )
    )tbl
    where rank = @rank - 1;

You can get your desired output using a query like this. :) thanks

sql store nested select subquery resultset into variable

1 Answers1