CODEWITHSUNDEEP
mysqlsql
user
user

Reputation: 545

Search a combination of 3 values

I want to search in a MySQL database a combination of three successives values Example, here is a table:

| id | value  |
| 1  | value0 |
| 2  | value1 |
| 3  | value2 |
| 4  | value3 |
| 5  | value4 |
| 6  | value0 |
| 7  | value1 |
| 8  | value2 |

I want to search, how many times there is, in the order a row containing "value0", and next row contains "value1" ans next row contains "value2". In the above data, the result will be 2 as we have twice value0, value1, value2 in three successives rows.

Is it possible with SQL ?

Thanks a lot.

Upvotes: 0

Views: 58

Answers (2)

ivan.sim
ivan.sim

Reputation: 9288

You can use ORDER BY to first order your rows, then perform an aggregation using GROUP BY. Try this sqlfiddle.

SELECT myvalue, COUNT(*)
FROM table1
GROUP BY myvalue
ORDER BY myvalue;

Upvotes: 0

Moneer Kamal
Moneer Kamal

Reputation: 1877

try this my friend

SELECT COUNT( value ) 
FROM test 
WHERE value =  'value0'
AND id +1
IN (

SELECT id
FROM test 
WHERE value =  'value1'
)
AND id +2
IN (

SELECT id
FROM test 
WHERE value =  'value2'
)

Upvotes: 2

Related Questions