Way to make query with inner join, for update of X skip locked, ordering and limit

Question

I need to acquire N records of table X, but I need to filter these records using an INNER JOIN, and order them on the joined records accordingly beforehand.

Example of what I tried to do (might not be 100% exact, as I'm using SQLAlchemy):

SELECT X.id
FROM X
   INNER JOIN Y ON X.id = Y.other_id
WHERE Y.condition_one
ORDER BY Y.condition_two
LIMIT 10
FOR UPDATE OF X SKIP LOCKED;

When I do this, I am getting fewer than 10 distinct ids (probably because the joined rows are limited), however, I cannot execute DISTINCT with FOR UPDATE. I think I can't use a subquery, because of FOR UPDATE.

What should I do?

Answer 1

I had trouble with using DISTINCT and ORDER BY at the same time.

The LATERAL join should have worked in theory, but while working on an implementation, I found another approach:

SELECT X.id
FROM X
   JOIN (SELECT X.id, MIN(Y.condition_two) AS ordering
         FROM X
            INNER JOIN Y ON X.id = Y.other_id
         WHERE Y.condition_one) AS sub_x
      ON X.id = sub_x.id
ORDER BY ASC(sub_x.ordering)
LIMIT 10
FOR UPDATE OF X SKIP LOCKED;

If I wanted to order using DESC, I would have to change MIN to MAX.