sql server - Paginate SQL results by certain column - Stack Overflow

admin管理员组

文章数量:1289496

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 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.

• sql
• sql-server
• select

Share Improve this question Follow edited Feb 25 at 11:58 kkamil4sz asked Feb 20 at 16:26 kkamil4szkkamil4sz 54311 gold badge55 silver badges2222 bronze badges 3

• 1 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 Commented Feb 20 at 18:32
• Then add that to your question, explaining why. – Dale K Commented Feb 20 at 18:34
• 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 Commented Feb 25 at 12:45

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 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.

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

本文标签： sql serverPaginate SQL results by certain columnStack Overflow