CODEWITHSUNDEEP
mysql
Buzz LIghtyear
Buzz LIghtyear

Reputation: 490

MySQL fetching rows based on condition within condition

I have the following below

 ---------------------------------
 |   Id       |  Value    | Flag |
 ---------------------------------
 |   1        |   23      |  0   |
 ---------------------------------
 |   1        |   24      |  1   |
 ---------------------------------
 |   2        |   30      |  0   |
 ---------------------------------

I require a query, which should fetch the last two rows. The condition here is IF two rows have the same Id then the output should fetch the row which has flag = 1 . Where as If there is only one row for an Id, then the row with Flag = 0 should be fetched. Thus the output for the given requirement will be 

 ---------------------------------
 |   1     |   24        |   1   |
 ---------------------------------
 |   2     |   30        |   0   |

Upvotes: 0

Views: 99

Answers (3)

fthiella
fthiella

Reputation: 49089

Other answers looks correct to me. This is a different approach:

SELECT t1.*
FROM
  yourtable t1 LEFT JOIN yourtable t2
  ON t1.Id = t2.Id AND t1.Flag=0 AND t2.Flag=1
WHERE
  t2.Id IS NULL

Please see fiddle here.

Upvotes: 1

Strawberry
Strawberry

Reputation: 33935

I prefer an uncorellated approach...

SELECT x.*
  FROM my_table x 
  JOIN 
     ( SELECT id,MAX(flag) max_flag FROM my_table GROUP BY id ) y 
    ON y.id = x.id 
   AND y.max_flag = x.flag;

Upvotes: 1

fancyPants
fancyPants

Reputation: 51938

SELECT
*
FROM yourTable yt1
WHERE Flag = (SELECT MAX(Flag) FROM yourTable yt2 WHERE yt1.Id = yt2.Id)
GROUP BY Id

Upvotes: 2

Related Questions