Get previous row and compare values I need to compare values of neighbouring rows in a table and get the number of times the value 42 of column calledstationid precedes the value 34. This is a system log table and calledstationid is not an auto-increment column.

I created this query but it has some problems:

SELECT COUNT(*) FROM (

    SELECT
        h.acctstarttime, h.acctstoptime, h.radacctid, h.calledstationid,
        (
            SELECT MAX(sub.calledstationid) FROM radius.radacct_historical as sub 
            WHERE sub.callingstationid = '54:9F:13:27:31:7F'
            AND sub.acctstarttime &lt; h.acctstarttime
        ) as previous,

    h.callingstationid

    CASE WHEN h.calledstationid = 34 AND previous = 42 THEN 'yes' END AS goal

    FROM radius.radacct_historical  AS h 
    WHERE h.callingstationid = '54:9F:13:27:31:7F'
    ORDER BY h.acctstarttime ASC;

) as total WHERE goal = 'yes'


First problem:

(
    SELECT MAX(sub.calledstationid) FROM radius.radacct_historical as sub 
    WHERE sub.callingstationid = '54:9F:13:27:31:7F'
    AND sub.acctstarttime &lt; h.acctstarttime
) as previous,


The previous calledstationid is not returned properly. I get the correct value only in the first 2 results, thereafter it seems that MySQL stores the value of the result in memory and does not update the subquery.

Second problem:

CASE WHEN h.calledstationid = 34 AND previous = 42 THEN 'yes' END AS goal


The column name previous in the CASE expression is not recognised.

Could anyone shed any light on how to do this?

http://www.sqlfiddle.com/#!2/774ff/42/1
You have replaced the previous column reference in the CASE expression with the same subquery that you are using earlier to calculate the value of previous. While that certainly resolves the issue of referencing a calculated column at the same nesting level, duplication of code is far from an ideal solution.

You can avoid the duplication by abandoning the CASE expression completely as unnecessary. All it does is producing a "yes"/"no" result based on the values of the current and previous calledstationid only to be compared to "yes" at the outer level. Instead of checking on the value of achou, your outer level can be checking on the values of calledstationid and previous directly, like this:

SELECT COUNT(achou) as total
FROM (
    SELECT
        h.calledstationid,
        (
            SELECT calledstationid
            FROM radius.radacct_historical as sub 
            WHERE sub.callingstationid = '54:9F:13:27:31:7F'
              AND sub.acctstarttime &lt; h.acctstarttime
            ORDER BY sub.acctstarttime DESC
            LIMIT 1 
        ) as previous,
    FROM radius.radacct_historical  AS h 
    WHERE h.callingstationid = '54:9F:13:27:31:7F'
) as S WHERE S.calledstationid = 34 AND S.previous = 42;


As you can see, I have also removed other elements from the query, like most of the columns returned by the nested SELECT (acctstarttime, acctstoptime, radacctid, callingstationid – they neither participate in the output nor affect the count result) and the nested ORDER BY h.acctstarttime ASC (there is no need to sort rows before counting them).