CODEWITHSUNDEEP
sqlsqlite
Secolink Eu
Secolink Eu

Reputation: 95

SQLite: Use subquery result in another subquery

I have following table with data

id | COL1
=========
1  |  b 
2  |  z 
3  |  b 
4  |  c 
5  |  b 
6  |  a 
7  |  b 
8  |  c 
9  |  a

So i know ID of 'z' (ID = 2) in the table and i will call it Z_ID. I need to retrieve rows between 'a' and 'c' (including 'a' and 'c'). It must be first 'a' that comes after Z_ID. 'c' must come after Z_ID and after 'a' that i found previously.

Result that i am seeking is:

id | COL1
=========
6  |  a 
7  |  b 
8  |  c

My SELECT looks like this

SELECT * FROM table WHERE id >= ( SELECT MIN(ID) FROM table WHERE COL1 = 'a' AND ID > 2 ) AND id <= ( SELECT MIN(ID) FROM table WHERE COL1 = 'c'AND ID > 2 and ID > ( SELECT MIN(ID) FROM table WHERE COL1 = 'a' AND ID > 2 ) )

I am getting the result that i want. But i am concerned about performance because i am using same subquery two times. Is there a way to reuse a result from first subquery? Maybe there is cleaner way to get the result that i need?

Upvotes: 0

Views: 1024

Answers (2)

forpas
forpas

Reputation: 164099

Use a CTE which will return only once the result of the subquery that you use twice:

WITH cte AS (
  SELECT MIN(ID) minid
  FROM tablename
  WHERE COL1 = 'a' AND ID > 2  
)  
SELECT t.*
FROM tablename t CROSS JOIN cte c
WHERE t.id >= c.minid
AND t.id <= (
  SELECT MIN(ID)
  FROM tablename
  WHERE COL1 = 'c' and ID > c.minid
)

In your 2nd query's WHERE clause:

WHERE COL1 = 'c'AND ID > 2 and ID > (...

the condition AND ID > 2 is not needed because the next condition and ID > (... makes sure that ID will be greater than 2 so I don't use it either in my code.
See the demo.
Results:

| id  | COL1 |
| --- | ---- |
| 6   | a    |
| 7   | b    |
| 8   | c    |

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269803

You can use window functions for this:

select t.*
from (select t.*,
             min(case when id > min_a_id and col1 = 'c' then id end) over () as min_c_id
      from (select t.*,
                   min(case when col1 = 'a' then id end) over () as min_a_id
            from (select t.*,
                         min(case when col1 = 'z' then id end) over () as z_id
                  from t
                 ) t
            where id > z_id
           ) t
     ) t
where id >= min_a_id and id < min_c_id;

Upvotes: 1

Related Questions