Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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)
share|improve this question

2 Answers 2

up vote 1 down vote accepted

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
;
share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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