I'm sure my problem is quite simple to solve, but I've been on it for a while and I could use some help.
I have a huge table, but the problem boils down to this: some items have 2 versions (one normal and one upgraded), and I'd like to be able to show only the best version of an item (upgraded if it exists, the normal one otherwise).
Name | Value | Type Tool A | 50 | Tool A Tool A [U] | 75 | Tool A Tool B | 100 | Tool B Tool C | 200 | Tool C Tool B [U] | 150 | Tool B
And I'd like to get
Name | Value | Type Tool A [U] | 75 | Tool A Tool C | 200 | Tool C Tool B [U] | 150 | Tool B
Note: there can only be one upgrade, and the base version of the "tool" always exists if that helps.
I can easily select the best value for tool A, B or C, but selecting the name (and other properties) with it is the problem.
I tried something like
SELECT A, MAX(B), C GROUP BY C, but it's throwing errors back at me.