Check value of last 4 rows-CodePudding

I want to check the value of the last 4 rows from DB. If these 4 rows have a specific value, Do something.

So the table looks like this:

______________
| id | value |
|____|_______|
| 1  |   a   |
|____|_______|
| 2  |   a   |
|____|_______|
| 3  |   a   |
|____|_______|
| 4  |   a   |
|____|_______|
| 5  |   a   |
|____|_______|
| 6  |   a   |
|____|_______|

Here is a fiddle to test with: http://sqlfiddle.com/#!9/1064b3/1

I can run the following query SELECT * FROM testing ORDER BY id DESC LIMIT 4, Then check with PHP:

//Count number of `a` from last 4 rows
$count = 0;

foreach($rows as $row){
    if($row['value'] == 'a'){
        $count  ;
    }
}

//If last 4 rows value = 'a'
if($count == 4){
    //Do something 
}

Is there is a better way with SQL to get the rows only if the 4 rows value = a or return true or false maybe?

CodePudding user response：

A multitude of ways, you could just use simple aggregation to count the rows:

select case when 
    count(case when value='a' then 1 end)=Count(*) 
  then 'true' else 'false' 
  end Last4AreA
from (
    select value
    from t
    order by id desc
    limit 4
)x

CodePudding user response：

Try this:

 SELECT COUNT(a.value)
 FROM(
      SELECT value
      FROM testing
      ORDER BY id DESC LIMIT 4
 ) AS a
 WHERE a.value = 'a'

If 3 is returned, it's TRUE (You have one not equal to A), FALSE otherwise.