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))
精彩评论