News for hackers. Stuff that matters.
Re:User-defined Order in SQL by gnosek on Wednesday March 21, 2018 @04:56AM (#16636480)
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.

There are no comments on this comment yet.