开发者

mysql query with enums

开发者 https://www.devze.com 2023-03-27 21:07 出处:网络
I have the following 3 rows in my database table IDresIDuserIDsourceeventposaward 3239237968365181200qualified

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

精彩评论

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