SQL Offset return last row if no match

Question

I want to return the book that matches the offset/order and if there isn't one I want to return the last book.

WITH BOOKS AS (
    SELECT 'book_1' AS name, 0 AS order_ UNION ALL
    SELECT 'book_2', 1 UNION ALL
    SELECT 'book_3', 2
)

SELECT name from BOOKS ORDER BY order_ asc OFFSET 0 limit 1;

It should return:

Offset 0 - book_1

Offset 1 - book_2

Offset 2 - book_3

Offset 3+ - book_3

It works fine for offset 0,1,2 but not really sure how I can make it work for offset bigger than 3.

Fiddle

Thanks

Answer 1

If when you will create your own function which returns table data then you can do it easily using the if-exists (or you can use any logic) statements in the function. But doing this using one query is more difficult. For that, I choose the second way, so using a query.

My Sample Query:

with mytable as materialized
(
    select  
        *, 
        row_number() over (order by order_) as r_num 
    from 
        test.test_table
)
select * from (
    (SELECT * from mytable order by r_num offset 17 limit 1)
    union 
    (SELECT * from mytable where r_num = (select max(r_num) from mytable))
) t1 
limit 1;

This is my logic variant, maybe someone has another best variants.