I want to return the entire block (which is grouped by TelNO) if the Type= a when the rank=1 . Expected output is as follows.

I am trying the following code. But it gives only the record which satisfices the condition. I need the entire partition to appear. Since there's no aggregation function to perform I am struggling how the partition function can use to get relevant output

select *
from table
where Type=a and rank=1
group by TelNo

CodePudding user response：

This is the way I understood the question:

SQL> with test (telno, type, rank) as
  2    (select 76567, 'a', 1 from dual union all
  3     select 76567, 'b', 2 from dual union all
  4     select 76567, 'c', 3 from dual union all
  5     --
  6     select 56597, 'b', 1 from dual union all
  7     select 56597, 'a', 2 from dual union all
  8     --
  9     select 97658, 'b', 1 from dual union all
 10     select 97658, 'a', 2 from dual union all
 11     select 97658, 'b', 3 from dual union all
 12     --
 13     select 76567, 'a', 1 from dual union all
 14     select 76567, 'a', 2 from dual
 15    )
 16  select *
 17  from test
 18  where telno in (select telno from test
 19                  where type = 'a'
 20                    and rank = 1
 21                 );

     TELNO T       RANK
---------- - ----------
     76567 a          2
     76567 a          1
     76567 c          3
     76567 b          2
     76567 a          1

SQL>