Search code examples
sqloracle-databaseoracle11gpaginationrownum

Oracle 11g: LIMIT OFFSET on GROUPED data

I have the following queries and result sets

enter image description here

First data is un-grouped and the second is grouped by "category". My goals is to use pagination on this grouped data however since Oracle 11g does not support LIMIT and OFFSET, it has become a problem.

I looked into this question for ideas;

How to add offset in a "select" query in Oracle 11g?

However I don't want to use the WHERE clause because it would exclude records from the group. 

SELECT MAX(tb_test_1.category) as category, COUNT(tb_test_1.category) as count 
FROM tb_test_1 
GROUP BY tb_test_1.category

Can someone tell me how to using LIMIT and OFFSET on the above query in Oracle 11g?

Thank you

Solution

  • This is the widely known question and probably a lot of answered duplicates exist, but anyway in your case this query should work:

    select * from (

    select rownum offset, rs.* from (

       SELECT MAX(t.category) as category, 
              COUNT(t.category) as count 
         FROM tb_test_1 t 
        GROUP BY t.category
        /* add order by clause here if needed */

   ) rs

) where rownum <= 10 /* limit */
    and offset >= 0 /* offset */