Oracle Forms

Analytics are good for things like this:

SQL> select name
  2        ,col1
  3  from
  4  (
  5     select name
  6           ,col1
  7           ,dense_rank() over (partition by name order by case col1
  8                                                               when 'Best' then 1
  9                                                               when 'Good' then 2
 10                                                               when 'Bad'  then 3
 11                                                               else 0
 12                                                          end) dr
 13     from   t
 14  )
 15  where dr = 1
 16  ;

N COL1
- ----------
A Best
B Good
C Bad
D Best

Message was edited by: SomeoneElse

(forgot the ELSE)

Awed and grateful. Thank you very much!

select name, Best(col1) from tbl
group by name

Ok, no joking:

select name, reverse(max(reverse(col1))) from tbl
group by name

select name, max(col1) keep (dense_rank first order by decode(lower(col1),'best',1,'good',2,'bad',3,4)) 
from <table> 
group by name

create table PointTable(Name,Col1) as
select 'A','Bad'   from dual union all
select 'A','Good'  from dual union all
select 'A','Best'  from dual union all
select 'B','Good'  from dual union all
select 'C','Bad'   from dual union all
select 'D','Bad'   from dual union all
select 'D','Best'  from dual union all
select 'E','Best'  from dual union all
select 'E','worst' from dual union all
select 'F','Bad'   from dual union all
select 'F','worst' from dual;

select Name,Col1
from (select Name,Col1,
      rank() over(partition by Name
                  order by case Col1
                           when 'Best'  then 1
                           when 'Good'  then 2
                           when 'Bad'   then 3
                           when 'worst' then 4 end) as Rn
      from PointTable)
where Rn = 1
order by Name;

or

select distinct Name,
First_Value(Col1)
over(partition by Name
order by case Col1
         when 'Best'  then 1
         when 'Good'  then 2
         when 'Bad'   then 3
         when 'worst' then 4 end) as Col1
      from PointTable
order by Name;

Name  COL1
----  -----
A     Best
B     Good
C     Bad
D     Best
E     Best
F     Bad

Well spotted.