2

I need to perform a select operation and get the id column's value (single value returned). Using this value, I need to perform two more selects on the same table using UNION ALL (in order to select limit n id < selected.id and limit n id > selected.id

I am currently failing in getting the value from 1st query to perform union all queries. How can I fix it?

SELECT * FROM table WHERE column_b = 'a value' # this query returns ID.
union all  
(select a.* from table as a where id < returned.id
order by id desc limit 5) 
union all  
(select a.* from table as a where id >= returned.id
order by id asc limit 15)
1

Why not actually perform this as

SELECT ID INTO @ReturnedID FROM table WHERE column_b = 'a value';
select * from table as a where id = @ReturnedID
union all  
select * from table as a where id < @ReturnedID order by id desc limit 5
union all  
select * from table as a where id >= @ReturnedID order by id asc limit 15
;
1

How about using a join in the unioned SELECTs:

(SELECT a.* 
 FROM table AS a
  JOIN table AS t2 ON (a.id < t2.id)
 WHERE t2.column_b = 'a value'
 ORDER BY id DESC LIMIT 5) 
UNION ALL
(SELECT a.*
 FROM table AS a
  JOIN table AS t2 ON (a.id >= t2.id)
 WHERE t2.column_b = 'a value'
 ORDER BY id ASC LIMIT 15)

Note that the order from the parts of UNION ALL might not be preserved in the result.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.