CODEWITHSUNDEEP
mysql
S.S
S.S

Reputation: 101

Selecting rows until a column value isn't the same

SELECT product.productID
    , product.Name
    , product.date
    , product.status 
FROM product 
    INNER JOIN shelf ON product.sheldID=shelf.shelfID 
WHERE product.weekID = $ID 
    AND product.date < '$day' 
    OR (product.date = '$day' AND shelf.expire <= '$time' ) 
ORDER BY concat(product.date,shelf.expire)

I am trying to stop the SQL statement at a specific value e.g. bad.

I have tried using max-date, but am finding it hard as am making the time stamp in the query. (Combining date/time)

This example table shows that 3 results should be returned and if the status "bad" was the first result than no results should be returned. (They are ordered by date and time).

ProductID   Date     status
1        2017-03-27  Good
2        2017-03-27  Good
3        2017-03-26  Good
4        2017-03-25  Bad
5        2017-03-25  Good

Think I may have fixed it, I added this to my while loop.

The query gives the results in order by present to past using date and time, this while loop checks if the column of that row is equal to 'bad' if it is does something (might be able to use an array to fill it up with data). If not than the loop is broken.

I know it doesn't seem ideal but it works lol

   while ($row = mysqli_fetch_assoc($result)) {
    if ($row['status'] == "bad") {
    $counter += 1;
    }
    else{
break;}

Upvotes: 1

Views: 423

Answers (1)

Jorge Campos
Jorge Campos

Reputation: 23361

I will provide an answer just with your output as if it was just one table. It will give you the main ideia in how to solve your problem.

Basically I created a column called ord that will work as a row_number (MySql doesn't support it yet AFAIK). Then I got the minimum ord value for a bad status then I get everything from the data where ord is less than that.

select y.* 
  from (select ProductID, dt, status, @rw:=@rw+1 ord
          from product, (select @rw:=0) a
         order by dt desc) y
 where y.ord < (select min(ord) ord
                  from (select ProductID, status, @rin:=@rin+1 ord
                          from product, (select @rin:=0) a
                         order by dt desc) x
                 where status = 'Bad');

Result will be:

ProductID        dt     status  ord
-------------------------------------
1           2017-03-27   Good    1
2           2017-03-27   Good    2
3           2017-03-26   Good    3

Also tested with the use case where the Bad status is the first result, no results will be returned.

See it working here: http://sqlfiddle.com/#!9/28dda/1

Upvotes: 3

Related Questions