CODEWITHSUNDEEP
mysql
Jake N
Jake N

Reputation: 10583

Selecting the 'next' row given specific criteria in MySQL

I have this set of data and need to base my queries on what is in the 'next' logical row.

 id |  area |  order
 1     front   1
 2     back    2
 3     left    3
 4     middle  4
 5     right   5

I had a query such as this to count the number of instances where a front area was followed by back area

SELECT 
    (SELECT COUNT(id) 
     FROM table AS t2 
     WHERE t2.area = 'back' AND t2.order = (t1.order +1)) AS c,  
FROM table AS t1
WHERE t1.area = 'front'

But, the data set has now changed and could look something like this

 id |  area |  order
 1     front   1
 2     back    3
 4     left    4
 6     middle  7
 9     right   9

Now the IDs and order are not incrementing, some data has been removed and then re-added.

How can I write the same query again given this new data set?

Upvotes: 0

Views: 186

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270873

Your original query is rather clever. Here is a slightly different approach. It gets the next area in a subquery (using a correlated subquery as in your example). It then counts the rows where the conditions are true:

select sum(case when area = 'Front' and nextarea = 'Back' then 1 else 0 end)
from (SELECT t1.*,
             (SELECT t2.area 
              FROM table t2 
              where t2.order > t1.order
              order by t2.order
              limit 1
             ) as nextarea 
FROM table t1;

This query is more expensive that yours. You were able to use a nice equality condition on the order column. Here, a sort is needed with the limit to get the next value. A composite index on (order, area) should help performance.

Upvotes: 1

Related Questions