CODEWITHSUNDEEP
sqldatabaseoracle-database
Chris Hill
Chris Hill

Reputation: 195

Selecting All Rows Matching Matching First 10 IDs in Oracle

This is a pagination related issue in Oracle. I have two tables, LIST and LIST_ITEM with a one-to-many relationship. I'm trying to implement pagination on the number of lists, where each LIST can contain a variable number of LIST_ITEM. Essentially, I need to grab all rows from LIST_ITEM matching the first N LIST ids. Any thoughts on implementing this in Oracle DB? Ideally without adding a separate query.

Previously I was using JPA EntityManager to implement pagination using setFirstResult() and setMaxResults(), but because the number of rows this query should return is variable, that will no longer work for me.

Upvotes: 0

Views: 249

Answers (1)

Alex Poole
Alex Poole

Reputation: 191265

You can use an analytic function like dense_rank() in a subquery to rank the IDs, and then filter on the ranks you want, e.g.:

select id, col1, col2
from (
  select id, col1, col2, dense_rank() over (order by id) as rnk
  from list_items
)
where rnk <= 10

or for later pages

select id, col1, col2
from (
  select id, col1, col2, dense_rank() over (order by id) as rnk
  from list_items
)
where rnk > 10 and <= 20

If you have ID in the list table with no IDs, and you want to take those into account, then you can use a subquery against that table and join (which lets you include other list columns too):

select l.id, li.col1, li.col2
from (
  select id, dense_rank() over (order by id) as rnk
  from list
) l
left join list_items li on li.id = l.id
where l.rnk <= 10;

If you're on Oracle 12c or high you can use the [row limit clause] enhancements to simplify that:

select l.id, li.col1, li.col2
from (
  select id
  from list
  order by id
  fetch next 10 rows only
) l
left join list_items li on li.id = l.id;

or for the second page:

  offset 10 rows fetch next 10 rows only

Upvotes: 2

Related Questions