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

Group by level code as I need all account from smallest level code per PortfolioNumber which have Failed status..hence kept min there..but not sure if this correct way to use — Aarav Saklecha
– Aarav Saklecha, Commented Aug 15, 2020 at 18:01
Is there a case that all 3 AccountNumbers do not exist for each LevelCode? — forpas
– forpas, Commented Aug 15, 2020 at 18:11
For PortfolioNumber = 456 why do you want all 3 rows in the results? LevelCode = 1 is the min which contains Status = failed. — forpas
– forpas, Commented Aug 15, 2020 at 18:19

forpas · Accepted Answer · 2020-08-15 18:27:30Z

1

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

answered Aug 15, 2020 at 18:27

forpas

165k10 gold badges51 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Aarav Saklecha Over a year ago

Wow, you were so quick and correct ...Thanks for helping appreciate did not know about this over (partition by PortfolioNumber)..Thanks again

Aarav Saklecha Over a year ago

@forpas- Small issue with this which I missed to mention, incase for particular PortfolioNumber if all Level code are passed (that is sum(track) =0 ),with the above we wont be showing that Portfolio itself .. for this scenario I wanted to show the max (levelcode) for that portfolio..Sorry missed to mention earlier..tried updating in fiddle but not getting desired result..Please suggest

Aarav Saklecha Over a year ago

With assumption that always lower level code will be first all passed , then only after that higher level code accounts will be passed per portfolio Number...means level1 all passed..level 2 some failed..then level 3 some failed

forpas Over a year ago

Check this: dbfiddle.uk/…

Aarav Saklecha Over a year ago

I was close to it above query I pasted ..but u query works well ..concise and clear..You are awesome :-)

|

Gordon Linoff · Accepted Answer · 2020-08-15 19:12:07Z

0

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

answered Aug 15, 2020 at 19:12

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

Oracle group by issue with sql query

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related