On: User-defined Order in SQL

[gnosek]

Two ways to do this with window functions (there's probably a better one but this is what I came up with quickly):

1. Subquery

    SELECT * FROM (
        SELECT u.user, i.text, row_number() OVER (PARTITION BY i.user ORDER BY i.pos) AS row
        FROM users u INNER JOIN items i USING(user)
    ) numbered
    WHERE row <= 3;

2. CTE

    WITH numbered AS (
      SELECT u.user, i.text, row_number() OVER (PARTITION BY i.user ORDER BY i.pos) AS row
      FROM users u INNER JOIN items i USING(user)
    )
    SELECT * FROM numbered WHERE row <= 3;

Same thing, really. CTE looks a bit clearer, subquery seems to generate a faster plan but I don't really have a large enough dataset handy for it to make a difference.