Oracle Fetch Next Row Limit by unique id's-CodePudding

I have a table having data as shown below,

 ------- ---------------- ---------------- 
|  Id   |    HierUnitId  |  ObjectNumber  |
 ------- ---------------- ---------------- 
|  10   |     3599       |     1          |
|  10   |     3599       |     2          |
|  20   |     3599       |     3          |
|  20   |     3599       |     4          |
|  20   |     3599       |     1          |
|  30   |     3599       |     2          |
|  30   |     3599       |     3          |
 ------- ---------------- ----------------

I have a select query

SELECT ID FROM TEST
FETCH NEXT :LIMIT ROWS ONLY

Now I want to limit the number of rows using the value of limit. When the value of Limit is 2 I want two distinct id's i.e up to 5 rows. However, from query I will get only two rows having 10 as the id. Can someone help me in limiting the rows using distinct id? What i want is total number of distinct id in the output is limit.

CodePudding user response：

Use the DENSE_RANK analytic function to number the rows based on the unique/distinct ID values and then filter on that:

SELECT id
FROM   (
  SELECT ID,
         DENSE_RANK() OVER (ORDER BY id) AS rnk
  FROM   test
)
WHERE  rnk <= 2;

Which, for the sample data:

CREATE TABLE test (Id, HierUnitId, ObjectNumber ) AS
SELECT 10, 3599, 1 FROM DUAL UNION ALL
SELECT 10, 3599, 2 FROM DUAL UNION ALL
SELECT 20, 3599, 3 FROM DUAL UNION ALL
SELECT 20, 3599, 4 FROM DUAL UNION ALL
SELECT 20, 3599, 1 FROM DUAL UNION ALL
SELECT 30, 3599, 2 FROM DUAL UNION ALL
SELECT 30, 3599, 3 FROM DUAL;

Outputs:

ID

10

10

20

20

20

ID
10
10
20
20
20

db<>fiddle here

CodePudding user response：

Am I getting you wrong or is this what you want?

select * from test
where id <= 2;

Or in case there can be gaps in your id, a join will work:

select  table_name.id, table_name.HierUnitId, table_name.ObjectNumber from
(select distinct id from table_name
order by id FETCH NEXT 2 ROWS ONLY) t
inner join
table_name
on t.id = table_name.id
;

CodePudding user response：

As you said in the comment, you need to be able to define how many distinct ids should be shown. For that case i'd recommend you to find those ids first (see the distinct_ids part) and fetch all the lines you needed afterwards

with distinct_ids as (
  select distinct id
    from test_data  
   order by id
   fetch first :limit rows only)  

select id
  from test_data td
  join distinct_ids di
    on td.id = di.id