Select next or first row

Question

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?

score 3 · Accepted Answer · answered Mar 06 '21 at 13:40

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.

score 3 · Answer 2 · answered Mar 07 '21 at 01:13

Appending the default row with UNION ALL and LIMIT 1 will be simplest and fastest:

( -- parentheses required!
SELECT * FROM tbl WHERE id > 5 ORDER BY id LIMIT 1
)
UNION ALL
SELECT * FROM tbl WHERE id = 1  -- the default row
LIMIT 1;

Note the required set of parentheses. This way, the first LIMIT 1 applies to the first SELECT, while the second LIMIT 1 applies to the whole query.

In effect, the second SELECT will be "never executed" if the first SELECT returns a row, which avoids any additional cost while keeping the first (main) query simple. This, in turn, allows efficient index utilization, which will be the main performance gain! Check the query plan with EXPLAIN (ANALYZE) to see for yourself.

Since both LIMIT clauses are LIMIT 1, the first one is logically redundant. (We still need parentheses to glue ORDER BY to the first SELECT!) But it can help the query planer to optimize better (at least that's what I observed in older versions of Postgres). And the query may be easier to read.

Select next or first row

2 Answers2