SQL OR clause priority

Question

I have a table with user awards, which can be of various different types.

For example, here are the records for the qualification ID 94:

So as you can see, there are 2 users, one has records for the award type of "average", "min", "max" and "final", the other has the same but no "final" award.

What I want is to get only 1 row per user. If they have an award of type "final" I want that, otherwise I want the "average" one, I don't want "min" or "max" at all.

So as an example, here is the query with just a simple IN clause:

So based on that, what I want the result to be is for the user 34562 I want the row with the "final" award, and for the user 6256 i want the row with their "average" award, since they don't have a "final" record.

I'm sure this should be fairly simple, but i'm failing miserably this morning.

I think I should be able to select the final record, then do a UNION ALL, but I can't seem to work it out in my head. Can anyone point me in the right direction?

I should point out that whilst this is MySQL for me, it needs to be compatible with other database platforms.

Thanks.

Answer 1

An easy way would be to check if an average entry exists in the where clause:

SELECT * FROM Table t
WHERE qualid = 94
and (type = 'average' AND
    not exists(SELECT * FROM Table t2
        WHERE t.qualid=t2.qualid AND t.userid=t2.userid AND type = 'final') 
    OR type = 'final')