Select next or first row

For a given ID, I'm trying to get the next row or if not present (end of list), return the first one.

ID (primary key)    value
 1                   John
 3                   Bob
 9                   Mike
 10                  Tom

• If we look for 5, it should return the row with ID 9.

• If we look for 10, it should return the row with ID 1.

Currently, I simply run two queries, first getting the next:

SELECT * FROM table WHERE id > 5 order by id limit 1;

and then if this returns no result, return the first:

SELECT * FROM table WHERE id = 1;

But I was wondering if there was a way to do it in one go?

You can combine the two queries:

with t as (
  select *
  from the_table 
  where id > 5 
  order by id limit 1
)
select *
from t
union all
select *
from the_table
where id = 1 
and not exists (select *
                from t);

Another option is to use an OR condition:

select *
from the_table
where (id > 5  or id = 1)
order by id = 1, id
limit 1

The order by id = 1 will sort the row with id = 1 at the end and all others (where that condition is false) will be sorted by the actual id value. The disadvantage is that you need to repeat the search condition

Typically OR conditions aren't optimized well, so the first alternative might be faster.

StackExchange Database Administrators Q#286568, answer score: 3