In Oracle, I can select the top 1 message in a sorted table with
select messageid from(
select
messageid,
RANK() over (order by messageid asc) as msg_rank
from messages
) where msg_rank=1;
And as I discovered in a previous question I can select a row exclusively with
select * from messages where rownum < 2 for update skip locked;
However I can't merge these two concepts together
select messageid from(
select
messageid,
RANK() over (order by messageid asc) as msg_rank
from messages
) where msg_rank=1 for update skip locked;
-- results in error
-- ORA-02014: cannot select FOR UPDATE from view with DISTINCT, GROUP BY, etc.
How can I select the top N with readpast locking?
Will this work?
select messageid from messages
where messageid in (
select messageid from(
select
messageid,
RANK() over (order by messageid asc) as msg_rank
from messages
) where msg_rank=1
)
for update skip locked;