开发者

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    2379开发者_StackOverflow中文版6   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


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
    )


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

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜