开发者

MySQL: How to return only one row based on criteria within a resultset

开发者 https://www.devze.com 2023-04-05 07:51 出处:网络
I have the following table: id | group | value 1|1|10 2|1|20 3|1|30 4|0|20 5|0|20 6|0|10 I want to return the h开发者_如何学Cighest value where the group is 1 (=30) and all of the values where the

I have the following table:

id | group | value 
1  |   1   |   10
2  |   1   |   20
3  |   1   |   30
4  |   0   |   20
5  |   0   |   20
6  |   0   |   10

I want to return the h开发者_如何学Cighest value where the group is 1 (=30) and all of the values where the group is 0, into one resultset.

I have to do this in one statement, and I guess I should use an IF statement within a SELECT statement, but I can't work out how. Can anyone help to point me in the right direction?


(select max(value) from the_table where group = 1)
union
(select value from the_table where group = 0)


If (group +value) is unique, you can also do it without union (as proposed by Ray Toal)

SELECT a.value
FROM table1 a    
WHERE a.`group`=0 or (a.`group`=1 AND a.value = 
 (SELECT MAX(value) FROM table1 b WHERE b.`group`=1))
0

精彩评论

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