How to select up to x rows when records have to be grouped based on criteria

Question

I want to create query where I select up to x rows, records are grouped by one id, and whole groups have to be result of the query. Values I base filtering on are stored in p_id column, rows with the same value create groups. In case of that table:

    p_id    age
0   00170   64  
1   00170   64  
2   00201   24  
3   00201   64  
4   00201   64  
5   00300   24  
6   00300   20

I want to get select 4 rows, but because groups with p_id's 00170,00201 are total 5 records I get:

0   00170   64 
1   00170   64

If I would select 5 rows i would get:

0   00170   64 
1   00170   64 
2   00201   24 
3   00201   64 
4   00201   64

If I would select 6 rows I would get (p_id 00300 is 2 records so not included as sum exceeds 6):

0   00170   64 
1   00170   64 
2   00201   24 
3   00201   64 
4   00201   64

So whole groups are returned. I'm working with oracle db, selecting x rows is easy with ROWNUM. I get lost when I try to get up to certain amount of rows with additional criteria.

what have you tried so far, can you share some of your code ? Don't use rownum to limit the number of rows see https://stackoverflow.com/questions/470542/how-do-i-limit-the-number-of-rows-returned-by-an-oracle-query-after-ordering — Koen Lostrie, Jan 17 '20 at 08:27
@Hrzug . . . It is unclear from your question if the `p_id` values are ordered. Or if there is another column that orders them. That is, do you always want the minimum value of `p_id` or do you want the "earliest" based on some other column in the table. — Gordon Linoff, Jan 17 '20 at 12:27

MT0 · Answer 1 · 2020-01-17T08:55:44.147

Oracle Setup:

CREATE TABLE test_data ( p_id, age ) AS
SELECT '00170', 64 FROM DUAL UNION ALL
SELECT '00170', 64 FROM DUAL UNION ALL
SELECT '00201', 24 FROM DUAL UNION ALL
SELECT '00201', 64 FROM DUAL UNION ALL
SELECT '00201', 64 FROM DUAL UNION ALL
SELECT '00300', 24 FROM DUAL UNION ALL  
SELECT '00300', 20 FROM DUAL;

Query:

Order the rows then find the maximum row number for each group and then filter to only return the groups whose maximum row number is contained in the row limit you desire:

SELECT p_id,
       age
FROM   (
  SELECT t.*,
         MAX( ROWNUM ) OVER ( PARTITION BY p_id ) AS grp
  FROM   (
    SELECT *
    FROM   test_data
    ORDER BY p_id
  ) t
)
WHERE  grp <= 4;

Output:

P_ID  | AGE
:---- | --:
00170 |  64
00170 |  64

If you change the last line to 5 then it will return 5 rows and change it to 6 then it will still return 5 rows.

db<>fiddle here

score 2 · Answer 2 · answered Jan 17 '20 at 08:56

I would address this with a window count and filtering:

select p_id, age
from (select p_id, age, count(*) over(order by p_id) cnt from mytable t) t
where cnt <= 5
order by p_id

You can change cnt <= 5 as needed.

Demo on DB Fiddle:

cnt <= 4:

P_ID | AGE
---: | --:
 170 |  64
 170 |  64

cnt <= 5:

P_ID | AGE
---: | --:
 170 |  64
 170 |  64
 201 |  24
 201 |  64
 201 |  64

cnt <= 6:

P_ID | AGE
---: | --:
 170 |  64
 170 |  64
 201 |  24
 201 |  64
 201 |  64

score 0 · Answer 3 · answered Jan 17 '20 at 12:26

GMB's answer is fine. But it can be simplified a wee bit by using RANK(). This function happens to do exactly what you want:

select p_id, age
from (select t.*,
             rank() over (order by p_id) as rnk
      from t
     ) t
where rnk <= 5
order by p_id;

More importantly, though, if the p_id values are not ordered, then you might want an additional step: assign the minimum value of some ordering column to each p_id. Let me call that ordering column id:

select p_id, age
from (select t.*,
             rank() over (order by p_id_grp) as rnk
      from (select t.*, min(id) over (partition by p_id) as p_id_grp
            from t
           ) t
     ) t
where rnk <= 5
order by p_id;

score -1 · Answer 4 · answered Jan 17 '20 at 08:18

-1

This is a tipical Top-N query:

use ROWNUM with ordered view to get the ordering correct:

SELECT p_id, age
FROM   (SELECT p_id, age
        FROM   table
        ORDER BY age DESC)
WHERE ROWNUM <= 4;

For Oracle v 12c onward there is the new FETCH clause:

SELECT p_id, age
FROM   table
GROUP BY p_id
FETCH FIRST 4 ROWS ONLY;

More resources: https://oracle-base.com/articles/misc/top-n-queries

answered Jan 17 '20 at 08:18

m.rp

634
7
19

1

This is not the correct solution as per OPs intent. – Ankit Bajpai Jan 17 '20 at 08:30

How to select up to x rows when records have to be grouped based on criteria

4 Answers4