Q:

SQL return optimal value if

I'm working with a database in which a table like the one below appears.
 StudentNum  |    Coin
-------------+------------
     1       |    Heads
     1       |    Tails
     3       |    Tails
     3       |    Tails
     2       |    Heads
     3       |    Tails

I do not have permissions to add or adjust the table, and need to know if it's possible to control the conditions on which rows are populated when I join this to another table.
Specifically, I am trying to find if each given ID has obtained a positive result (in this case Tails) at least once. If so, use that for the field, otherwise use Heads.
 StudentID   |    Coin   |    ...
-------------+-----------+----------
     1       |    Tails  |
     2       |    Heads  |
     3       |    Tails  |
     4       |     ...   |
    ...      |     ...   |

A:

You can use a CASE WHEN statement. See if this is what you want.
SELECT distinct a.StudentID,
                b.Coin

FROM table a
Join ( Select Student ID, 
               CASE WHEN Coin = 'Tails' then 'Tails'
               Else 'heads'
               END Coin
        From table ) b on b.student_id = a.student_id

