Select 'best' row?
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