Comparing SQL strings calculating discrepancies in value

Question

I have a table:

id     firstval    secondval     
1         4            5
2         5            4
3         3            3
4         6            6
5         7            8
6         9            8
7         3            3
8         3            3

The first thing I need to do is count the number of times secondval > firstval. This is obviously not a problem.

However, what I'm struggling with is how to count how many times (for each instance secondval > firstval) the next line satisfies the conditionsecondval < firstval

So, in this example, there are two lines that would satisfy the first rule id 1 and 5 and two for the second rule, the next lines are id 2 and 6.

Answer 1

SELECT id, @prevGreater AND secondval < firstval AS discrepancy, 
       @prevGreater := secondval > firstval AS secondGreater
FROM (SELECT * FROM YourTable ORDER BY id) AS x
CROSS JOIN (SELECT @prevGreater := false) AS init

Demo

Answer 2

SELECT * from table t1
INNER JOIN table t2 on t1.ID+1=t2.ID -- here we join on t2.ID is t1.ID+1
WHERE t1.secondval>t1.firstval AND t2.secondval<t2.firstval

Now you can use the COUNT statement as you want :)

Answer 3

DECLARE @YourTable TABLE 
(id int, firstval int, secondval int)

INSERT INTO @YourTable
SELECT 1, 4, 5
UNION ALL
SELECT 2, 5, 4
UNION ALL
SELECT 3, 3, 3
UNION ALL
SELECT 4, 6, 6
UNION ALL
SELECT 5, 7, 8
UNION ALL
SELECT 6, 9, 8
UNION ALL
SELECT 7, 3, 3
UNION ALL
SELECT 8, 3, 3

SELECT  ID
,CASE 
    WHEN SECONDVAL>FIRSTVAL THEN 0
    WHEN FIRSTVAL>SECONDVAL THEN 1
    ELSE 0
END AS DISCREPANCY
,CASE 
    WHEN SECONDVAL>FIRSTVAL THEN 1
    WHEN FIRSTVAL>SECONDVAL THEN 0
    ELSE 0
END AS SECONDGREATER

FROM    @YourTable

.