Oracle Query - Generate Results In Order

What would you expect to see as a result from your sample data?

Just to be sure, can you post a sample of what you're looking for?  I can visualize several results that might satisfy your description and you undoubtedly have something specific in mind.

Kent

I'm a little confused  as to how empno 999 should be handled given that it has stage value of stage2 in both instances.

awging00
that was a typo
the second row for 999 should read stage3 not stage2

the results I'm looking for base on the data above would look like

EMPNO, VALUE
100, RESULT123
999, RESULT234
124, RESULT456
234, RESULT789

try this:

drop table tab1 purge;
create table tab1(EMPNO number, STAGE varchar2(20), VALUE varchar2(20));

insert into tab1 values(100,'STAGE1','RESULT123');
insert into tab1 values(999,'STAGE2','RESULT234');
insert into tab1 values(999,'STAGE2','RESULT345');
insert into tab1 values(124,'STAGE2','RESULT456');
insert into tab1 values(124,'STAGE3','RESULT567');
insert into tab1 values(234,'STAGE1','RESULT678');
insert into tab1 values(234,'COMPLETE','RESULT789');
commit;

select empno, stage, value from
(
select empno, stage, value, row_number() over(partition by empno order by case stage when 'COMPLETE' then 1 when 'STAGE1' then 2 when 'STAGE2' then 3 when 'STAGE3' then 4 when 'STAGE4' then 5 end) rn
from tab1
)
where rn=1
/

Select allOpen in new window

slightwv

I don't have database write access, this is just a read only query to output results

>> I don't have database write access, this is just a read only query to output results

I just posted the create table for the test case.  Creating tables isn't a requirement.

The SELECT is what you are after.

A slight variation on slightwv's answer.  Taking advantage that what you are looking for is the lowest sorted alphabetically.

SELECT empno, 
       stage, 
       value 
FROM   (SELECT empno, 
               stage, 
               value, 
               Row_number() 
                 over ( 
                   PARTITION BY empno 
                   ORDER BY stage) rn 
        FROM   mytab) 
WHERE  rn = 1; 

Select allOpen in new window

johnsone
technically that would work but I can't order by stage because there are other stages I didn't list in my example.  I need to assign them a priority or deal with each stage individually to set an order.

>>because there are other stages I didn't list in my example

Then try mine?  You can explicitly set the order you want.

>>because there are other stages I didn't list in my example<<
Can you provide a more realistic sample of the stage values with explanations of how they would need to be prioritized?

Lets try this one instead, I think it will give a better idea

Database
EMPNO, STAGE, VALUE
100, STAGE1, RESULT123
999, STAGE2, RESULT234
999, STAGE3, RESULT345
124, STAGE2, RESULT456
124, STAGE3, RESULT567
234, STAGE1, RESULT678
234, COMPLETE, RESULT789
345, STAGE3, RESULT333
345, STAGE4, RESULT444
456, STAGE4, RESULT465
555, NOTSTARTED, RESULT0
111, MASTERED, RESULT999
111, COMPLETED, RESULT5
111, STAGE1, RESULT4
111, STAGE2, RESULT3
111, STAGE3, RESULT2
111, STAGE4, RESULT1

Expected Results
EMPNO, HIGHESTSTAGE, VALUE
100, STAGE1, RESULT123
999, STAGE2, RESULT234
124, STAGE2, RESULT456
234, COMPLETE, RESULT789
345, STAGE3, RESULT333
456, STAGE4, RESULT465
555, NOTSTARTED, RESULT0
111, MASTERED, RESULT999

Have you not tried my select?  If not, why not?  If you have, what isn't working with it?

You can assign whatever sort order you want to whatever stage you want.

>> I think it will give a better idea
We would need the sort rules for the different stages.  Better if you just go with my select since you know your rules.

slightvw
I have not tried it because it looks like the results are being ordered by the name of the stages which doesn't give the desires result of assigning a priority to each stage or setting an order by stage manually.

Perfect, thank you!

Based on your latest example, you could use
order by decode(STAGE,'MASTERED',1,'COMPLETE',2,'STAGE1',3,'STAGE2',4,'STAGE3',4,'STAGE4',5,'NOTSTARTED', x)
I wasn't sure how notstarted should be prioritized, but if highest make x = 0, if lowest make x= 6.
Just a slightly different approach to the case statement concept that has been accepted (rightly so).

How can I eliminate the numbers that I don't want?
Such as removing stage2-4 if state 1 exists so it only outputs one row.

>>How can I eliminate the numbers that I don't want?

I don't understand?

>>exists so it only outputs one row

Did you use my entire select in https:#a41658380 ?

The where rn=1 returns 1 row based on the order in the case statement.

It just seems like way more code than it could be

It is a single select with an inline view of a single select.

You need to CASE code since you can't use a natural sort order by stage name.

Now, if you had a natural order, you wouldn't need the case statement but you would still need the row_number piece to only return a single row.