Oracle group by issue with sql query

Question

PortfolioNumber LevelCode AccountNumber Status  track
123                2       A101          Failed  1
123                2       A102         Failed   1
123                2       A103         Passed   0

123                1       A101         Passed   0
123                1       A102         Passed   0
123                1       A103         Passed   0

123                3       A101        Failed    1
123                3       A102        Failed    1
123                3       A103        Failed    1

456                1       A406        Failed    1
456                1       A407        Passed    0  
456                1       A408        Failed    1

I have got this table by doing certain other join to other few tables,

What this table data denotes to - Portflio Number (123) is there and we have in total three AccountNumber. We have different LevelCode (here 1,2,3) and each level is tagged to portfolioNumber and will have same 3 accountnumber tagged too. and We also have status at each row...Have created track Column jsut to have Failed as 1 and Passed as 0 which I tried using later in My below query (This column can be deleted)

What I am trying to find -

Lowest level code set per unique Portfolio Number(which Comprises of all three accountsNumber for that set ) where either of Status is failed

Query - Select PortfolioNumber, LevelCode, min(LevelCode), sum(track) from (table data which I got via some other tables join..pasted above) group by PortfolioNumber, LevelCode having sum(track) > 0'

But this does not give the correct result and all columns, running out of options now

Desired Result What I am

PortfolioNumber LevelCode AccountNumber Status  track
123                  2     A101           Failed  1
123                  2     A102           Failed  1
123                  2     A103           Passed  0

456                  1     A406           Failed  1
456                  1     A407           Passed  0 
456                  1     A408           Failed  1

Using Oracle

Answer 1

I think I would just use a correlated subquery:

select t.*
from t
where t.levelcode = (select min(t2.levelcode)
                     from t t2
                     where t2.portfolionumber = t.portfolionumber and
                           t2.status = 'Failed'
                    );

This seems like the simplest solution and can be easily optimized with an index on (portfolionumber, status, levelcode).

If you prefer, a similar method using window functions is:

select t.*
from (select t.*,
             min(case when status = 'failed' then levelcode end) over (partition by portfolionumber) as min_failed_levelcode
      from t
     ) t
where levelcode = min_failed_levelcode

Answer 2

This query:

select distinct PortfolioNumber, 
       min(LevelCode) over (partition by PortfolioNumber) LevelCode
from tablename
group by PortfolioNumber, LevelCode
having sum(track) > 0

returns the PortfolioNumber/LevelCode combinations that you want.
Use it with the operator IN to get the rows of the table:

select * 
from tablename
where (PortfolioNumber, LevelCode) in (
  select distinct PortfolioNumber, 
         min(LevelCode) over (partition by PortfolioNumber) LevelCode
  from tablename
  group by PortfolioNumber, LevelCode
  having sum(track) > 0
)

See the demo.
Results:

> PORTFOLIONUMBER | LEVELCODE | ACCOUNTNUMBER | STATUS | TRACK
> --------------: | --------: | :------------ | :----- | ----:
>             123 |         2 | A101          | Failed |     1
>             123 |         2 | A102          | Failed |     1
>             123 |         2 | A103          | Passed |     0
>             456 |         1 | A406          | Failed |     1
>             456 |         1 | A407          | Passed |     0
>             456 |         1 | A408          | Failed |     1