Ray Li Ray Li - 1 month ago 6
MySQL Question

MySQL Select All Rows After 1st Row With Matching Value

Is there a way to select all rows after the first row with a matching value?

I have tried:

SELECT * FROM Table WHERE Ate=False ORDER BY Ate ASC LIMIT 1

SELECT * FROM Table WHERE Id>=1stqueryid, Fruit=Blueberry

For example, I have a table with a column of fruits and a boolean that says if they have been ate or not.

ID---Fruit-------------- Ate

1---Blueberry------True

2---Orange----------True

3---Blueberry------False

4---Peach------------True

5---Blueberry----- True

6---Blueberry------False

7---Blueberry------True

I would want to find the first row that had "Ate" = "False" AND "Fruit" = "Blueberry". Then, select the remaining rows that have "Blueberry".

The result should be:

ID---Fruit---------------Ate

3---Blueberry--------False

5---Blueberry--------True

6---Blueberry--------False

7---Blueberry--------True

Is there a way to consolidate the two queries?

Answer

you can just use subquery to get the first id which meet the condition of

fruit = 'Blueberry' AND ate = false 

the subquery look like this

id >= (SELECT id 
              FROM   Table 
              WHERE  fruit = 'Blueberry' 
                     AND ate = false 
              LIMIT  1) 

this subquery returns the first id of the row which meet the condition

fruit = 'Blueberry' AND ate = false 

the final query look like this

SELECT * 
FROM   Table 
WHERE  id >= (SELECT id 
              FROM   Table 
              WHERE  fruit = 'Blueberry' 
                     AND ate = false 
              LIMIT  1) 
       AND fruit = 'Blueberry' 

See Screen Shot