CODEWITHSUNDEEP
mysql
Vidya
Vidya

Reputation: 8207

mysql query with enums

I have the following 3 rows in my database table

ID      resID   userID  source  event   pos     award 
3239    23796   8365    18      120     0       qualified        
3670    23796   8365    18      120     1       Finalist         
3671    23796   8365    18      120     0       first

Now the award column is enum ( "first","Finalist","qualified" ) I need to get the best of the award from the 3 rows i.e 

ID      resID   userID  source  event   pos     award 
3671    23796    8365     18     120     0      first

I tried the following query 

SELECT * from sometable 
WHERE userID = 8365 
GROUP BY userID
having min(award+0)

But it is not working . Could some one give some pointers

Upvotes: 2

Views: 347

Answers (2)

Karolis
Karolis

Reputation: 9562

SELECT * 
FROM sometable 
WHERE userID = 8365
ORDER BY award
LIMIT 1;

Another way:

SELECT * 
FROM sometable s
WHERE 
    userID = 8365 AND
    award = (
        SELECT MIN(award) 
        FROM sometable 
        WHERE userID = s.userID
    )

Upvotes: 2

nobody
nobody

Reputation: 10645

SELECT * from sometable WHERE userID = 8365
    ORDER BY FIELD( award, 'first', 'Finalist', 'qualified' ) LIMIT 1;

Upvotes: 0

Related Questions