Mysql – Perform thesql limited select that wraps when it reaches the end of table

MySQLselect

I want to grab the next 2 rows from a table after a given id. This code works nicely however if the rows being selected right at the end of the database I need the select to wrap around so for instance in a table of rows 0 to N I will at some point want to pull out rows N and 0 and then a bit later pull out 0 and 1. Is it possible to make a MYSQL select wrap around like this?

SELECT `promo_id` 
FROM `promotions` 
WHERE (`expiry_date` >= '2017-03-21' AND `promo_id` > (SELECT `promo_id`  FROM `promoted_questions` WHERE `promo_id` = 8))
ORDER BY `promo_id` ASC
LIMIT 2

UPDATE for clarity:

At the moment my code just returns up to the end of the table rather than wrapping back around to get the first rows again. Here's an example of what I want:

ID
1
2
3
4
5

If I run the code above with the starting ID as 2 then get IDs 3 and 4 back – perfect. If I run it with the starting ID as 4 then all I get back is 5, not so perfect as I was 5 and 1. Likewise, if I use starting ID 5 then I want 1 and 2 back from the database.

Best Answer

I've set a rextester example whith this sample data:

drop table if exists promotions;
create table if not exists promotions (promo_id int, expiry_date timestamp);
insert into promotions values
(1, '20170101'),(2, '20170101'),(3, '20170101'),(4, '20170101'),(5, '20170101');

IMHO you should add two additional records and then limit final result to two rows. Use a UNION with your query and an addition query that simply returns the two first rows of promotions table.

Note: Add parentheses to both queries or you'll get an error.

(select  promo_id
from     promotions
where    promo_id > 4
order by promo_id
limit    2)
union
(select  promo_id
from     promotions
order by promo_id
limit    2)
limit 2
;

The final result:

promo_id  |expiry_date
----------|--------------------
   5      | 01.01.2017 00:00:00
   1      | 01.01.2017 00:00:00

Using you query

(SELECT `promo_id` 
 FROM `promotions` 
 WHERE (`expiry_date` >= '2017-03-21' AND `promo_id` > (SELECT `promo_id`  FROM `promoted_questions` WHERE `promo_id` = 8))
 ORDER BY `promo_id` ASC
 LIMIT 2
)
UNION ALL
(SELECT `promo_id`
 FROM `promoted_questions`
 ORDER BY `promo_id` ASC
 LIMIT 2
)
LIMIT 2;
Related Question