Oracle-sql : Query with hiererchical group by

Question

I have a table like:

   ID || Ent NAME  ||  FUNCTION
   1  || Dupuis    ||  Signatory
   1  || Daturt    ||  Decision Maker
   1  || Nobel     ||  (Null )
   2  || Karl      ||  Decision Maker
   2  || Titi      ||  (Null )
   3  || Cloves    ||  (Null )
   3  || Cardigan  ||  (Null )

I want to get the most "important" people in a pre -established hierarchy (Signatory > Decision Maker > (Null ) ) So the expected result is:

   ID Ent || NAME      || FUNCTION
       1  ||  Dupuis   || Signatory
       2  ||  Karl     || Decision Maker
       3  ||  Cardigan || (Null )

for the 3rd , i don't care of person selected .

I work in Oracle with extremely limited right , I can do that SELECT ( and this it is s*** ).

I have a solution bypass but it is extremely ugly and I am not satisfied:

(SELECT "ID Ent" max (NOM), max (FUNCTION) 
    FROM table 
    WHERE FUNCTION = 'Signatory' GROUP BY "ID Ent") 
UNION 
(SELECT "ID Ent" max (NOM), max (FUNCTION) 
    FROM table 
    WHERE FUNCTION = 'Decision Maker' 
        AND "ID Ent" not in (SELECT "ID Ent" FROM table WHERE FUNCTION = 'Signatory') 
    GROUP BY "ID Ent") 
UNION 
(SELECT "ID Ent" max (NOM), max (FUNCTION) 
    FROM table 
    WHERE FUNCTION = 'Decision Maker' 
        AND "ID Ent" not in (SELECT "ID Ent" in FUNCTION FROM table WHERE ('Signatory', 'Decision Maker')) 
    GROUP BY "ID Ent");

Do you have a better way to do this?

Answer 1

I would approach this using analytic functions:

select t.Id, t.Name, t.Function
from (select t.*,
             row_number() over (partition by id
                                order by (case when function = 'Signatory' then 1
                                               when function = 'Decision Maker' then 2
                                               else 3
                                          end)
                               ) as seqnum
      from table t
     ) t
where seqnum = 1;