Paginate SQL results by certain column

Question

I would like to paginate complex table by certain column. Lets say we have table as follows:

ID | Name | Genre
1  | A | fantasy
2  | A | medieval
3  | B | sci-fi
4  | C | comedy
5  | C | sci-fi
6  | C | romanse
7  | D | horror

Then if I used some modified version of the following query:

SELECT * FROM movies ORDER BY id OFFSET 1 ROWS FETCH NEXT 2 ROWS ONLY;

Instead of 2 I would like to get 4 rows (all rows for 2 movies omitting the first one, so all rows for movies B and C)

Additionally:

• If anyone asks - I cannot split the table. If i could change the database I would just have movies table and separate genre table. This way I could just paginate through movies table and get all genres for each movie.

• In code after retrieving all entries for certain movies page I can just transform it into a nested model. For me it would be the best if there was SQL group by version that works like the one in code so you don't have to SUM/MIN/MAX etc. other columns. If I GroupBy movie name then i would like to have an object with movie name and then nested table/list of all genres (e.g. {"name": "A", "genres":["fantasy","medieval"]})

• I use MSSQL but it would be nice if the solution was more versatile so it can be used in other RDBMS if I ever have to switch to a different one.

Answer 1

You could create a Common Table Expression to emulate "unique movies",
on which your SELECT would apply naturally:

WITH
    -- 1 entry per movie please:
    m AS (SELECT name, MIN(id) id FROM movies GROUP BY name),
    -- Then only entries 2 and 3 of this movie list:
    sel AS
    (
        SELECT * FROM m ORDER BY id OFFSET 1 ROWS FETCH NEXT 2 ROWS ONLY
    )
-- Finally we would like the details for those 2 selected movies:
SELECT movies.* FROM sel JOIN movies ON movies.name = sel.name ORDER BY movies.id;

You can see that in a fiddle.

Answer 2

You could use the dense_rank window function to assign a number per movie name, and then paginate by that value. E.g.:

SELECT id, name, genre
FROM   (SELECT id, name, genre,
               DENSE_RANK() OVER (ORDER BY name) AS rn
        FROM   movies) t
WHERE  rn IN (2, 3) -- or any other condition on the groups you want