Forum Stats

  • 3,734,034 Users
  • 2,246,862 Discussions
  • 7,857,003 Comments

Discussions

Select 'best' row?

ngilbert
ngilbert Member Posts: 97 Blue Ribbon
edited May 2008 in SQL & PL/SQL
This might be a pretty simple question but....

If I have a table like this:

Name Col1
A Bad
A Good
A Best
B Good
C Bad
D Bad
D Best

Can someone help me figure out a way to select out the one best row for each name such that I get

Name Col1
A Best
B Good
C Bad
D Best

The idea being that I need one row per name - if a Col1 = 'Best' row isn't found I use Col1 = 'Good' and if that isn't found I use Col1 = 'Bad'

Thanks for any help

Comments

  • SomeoneElse
    SomeoneElse Member Posts: 14,866 Silver Crown
    edited May 2008
    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)
  • ngilbert
    ngilbert Member Posts: 97 Blue Ribbon
    Awed and grateful. Thank you very much!
  • Vadim Tropashko-Oracle
    Vadim Tropashko-Oracle Posts: 1,223 Employee
    edited May 2008
    select name, Best(col1) from tbl
    group by name

    Ok, no joking:

    select name, reverse(max(reverse(col1))) from tbl
    group by name
  • 572471
    572471 Member Posts: 984 Green Ribbon
    edited May 2008
    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
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    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
  • 450441
    450441 Member Posts: 2,525
    select name, reverse(max(reverse(col1))) from tbl
    group by name
    Well spotted.
This discussion has been closed.