CODEWITHSUNDEEP
mysqlsqlgroup-by
unicorn
unicorn

Reputation: 13

Mysql how to select max id group by another id where boolean is false

I have the following table:

I would like to select all max(id) grouped by another_id but only if the max(id) line has the condition equals to FALSE.

I tried to SELECT MAX(id) WHERE condition IS FALSE GROUP BY another_id, but I got the previous id. For example, with the following table:

 id | another_id | condition
 1  |         42 |         0
 2  |         42 |         1
 3  |         31 |         0
 4  |         77 |         1

The only result I want is id == 3

Is there any way to do that?

In advance, thank you!

Upvotes: 0

Views: 1624

Answers (2)

Anvesh
Anvesh

Reputation: 7713

Try this inline view:

SELECT MAX(MaxID) FROM 
(SELECT MAX(ID) AS MaxID FROM TABLE GROUP BY another_id) AS T

Upvotes: 0

fancyPants
fancyPants

Reputation: 51928

SELECT
MAX(id)
FROM
Table1 t
GROUP BY another_id
HAVING SUM(`condition`) = 0

See it live in an sqlfiddle.

Upvotes: 1

Related Questions