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.

I tagged it. Currently I use microsoft sql but It would be nice if the solution could be used at least in one other RDBMS. — kkamil4sz
– kkamil4sz, Commented Feb 20 at 18:32
If you're building a new system, why aren't you able to split the table into two with proper relations? Right now, every "movie" fact will be repeated twice per genre. Are you gonna add actors in the same way later? — siggemannen
– siggemannen, Commented Feb 25 at 12:45

Guillaume Outters · Accepted Answer · 2025-02-20 22:52:31Z

2

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.

answered Feb 20 at 22:52

Guillaume Outters

7,5371 gold badge22 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

kkamil4sz Feb 25 at 11:39

Would this have better or worse performance than the other solution with DENSE_RANK?

Mureinik · Accepted Answer · 2025-02-20 16:36:08Z

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

answered Feb 20 at 16:36

Mureinik

316k54 gold badges402 silver badges406 bronze badges

3 Comments

kkamil4sz Feb 20 at 18:56

This could work except instead of WHERE rn IN (2, 3) I would use WHERE rn BETWEEN 2 AND 3. This way we could achieve pagination - we could calculate first number as page_number * page_size + 1 and second would be page_number * page_size + page_size. This is if we count pages from 0, if we count pages from 1 then we could just substract 1 from page_number.

Mureinik Feb 20 at 19:15

@kkamil4sz indeed, you can use any condition you want to rn, and I agree that using between "feels" more like proper pagination. Note, however, that you can't rely on the page size here - a movie may have multiple genres, an without some additional knowledge or constraint, you won't know how many rows you get back for N movies.

kkamil4sz Feb 25 at 11:50

That is the point of the question. On the UI I'm showing a list of movies, so if page size is 10 and user is on second page then I want to retrieve all entries for the 10 movies omitting first 10. It does not matter how many entries will be returned form the db. If 47 entries are returned but they contain only 10 unique movies that that is exactly what I want. Pagination based on movie column instead of just by number of rows. I cannot split this table into separate movies and genre tables. (In code I transform the returned entries into a nested model)

Collectives™ on Stack Overflow

Paginate SQL results by certain column

2 Answers 2

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related