Oracle SQL FETCH NEXT Across Several Rows

Question

I have a SQL statement that fetches the next 20 rows from a table. There's a field in the table called SERIES_ID which contains values that might be shared by several rows in the table, e.g.:

MY_TABLE
PRIMARY_KEY |  TEXT_FIELD | SERIES_ID
===================================
1           | sdfdsfdsfds |    1001
2           | sdafsdfdf   |    1002
3           | sdffghhjhj  |    1002
4           | rttrytrytyu |    1002
5           | qweqwret    |    1003
6           | gvcbvcbcv   |    1003

SELECT 
    PRIMARY_KEY, 
    TEXT_FIELD, 
    SERIES_ID
FROM MY_TABLE 
OFFSET 0 
FETCH NEXT 3 ROWS ONLY;

What I'd like to do is to change my SQL statement so that instead of fetching the next X amount of rows it will fetch the next X amount of SERIES_IDs from the table. Is it possible?

Answer 1

You'd need to use an analytic function (e.g. DENSE_RANK()) to label the series values with a number and then use that in the where clause, something along the lines of:

SELECT primary_key,
       text_field,
       series_id
FROM   (SELECT primary_key,
               text_field,
               series_id,
               DENSE_RANK() OVER (ORDER BY series_id) dr
        FROM   my_table)
WHERE  dr <= 3;

It does not look like it's possible to access values in the OFFSET/FETCH, so you'll have to use the dense_rank to do the pagination.

Answer 2

First, you should always use ORDER BY with FETCH FIRST. SQL tables and result sets (with no ORDER BY) represent unordered sets. So the results may not be stable.

Now, this doesn't do exactly what you are asking for, but it might do what you want. Oracle supports the WITH TIES options for FETCH NEXT. This would let you get all the data for the last series id:

SELECT PRIMARY_KEY, TEXT_FIELD, SERIES_ID
FROM MY_TABLE 
ORDER BY SERIES_ID
OFFSET 0 FETCH NEXT 3 ROWS ONLY WITH TIES;