Mysql – initial and last date of change

MySQL

I have a table with ID's, dates and values witch are the results of periodic tests.
I can find the latest test with the according result.
But I need also the initial date since when the same result is marked.

ID  Date       Value
01  2015-01-01  2
01  2015-02-01  3
01  2015-03-01  3
01  2015-04-01  2
01  2015-05-01  2
01  2015-06-01  1
01  2015-07-01  3
01  2015-08-01  3
01  2015-09-01  3

So here the last date and result for ID 01 are 2015-09-01 and 3
The initial date that I also need is 2015-07-01 as that is the initial dat of the last serie with value 3

How can I write this query?

I get the last date and score by ID using this query:

select * from tblScores s 
join (
    select id, max(Date) as MaxDate 
    from tblScores group by id
) Q on s.id = q.id s.date = q.maxdate 

Best Answer

These queries with variables simulates the ROW_NUMBER() Window Function (not supported by MySQL).

Query 1 ID

If you are working with only 1 ID at a time like in you sample, this query can be used:

SELECT ID, MIN(Date), MAX(Date)
FROM (
    SELECT *
        , @n := CASE WHEN @value <> Value THEN @n +1 ELSE @n END as n   
        , @value := Value 
    FROM data1
    CROSS JOIN (SELECT @value, @n := 0) v
    ORDER BY Date DESC
) as x
WHERE n = 0
GROUP BY ID
;

Output:

ID |              MIN(Date) |                   MAX(Date)
 1 | July, 01 2015 00:00:00 | September, 01 2015 00:00:00

Query several IDs

If you want the same result for several IDs, this query can be used:

SELECT ID, MIN(Date), MAX(Date)
FROM (
    SELECT *
        , @n := CASE 
            WHEN @ID <> ID THEN 1
            WHEN @value <> Value THEN @n +1 
            ELSE @n END as n   
        , @value := Value 
        , @ID := ID 
    FROM data2
    CROSS JOIN (SELECT @id, @value, @n := 0) v
    ORDER BY ID, Date DESC
) as x
WHERE n = 1
GROUP BY ID
;

Output:

ID |              MIN(Date) |                   MAX(Date)
 1 | July, 01 2015 00:00:00 | September, 01 2015 00:00:00
 2 | July, 01 2015 00:00:00 |      July, 03 2015 00:00:00

This SQL Fiddle contains both samples and queries.

Data used:

INSERT INTO data2
    (`ID`, `Date`, `Value`)
VALUES
    (01, '2015-01-01', 2),
    (01, '2015-02-01', 3),
    (01, '2015-03-01', 3),
    (01, '2015-04-01', 2),
    (01, '2015-05-01', 2),
    (01, '2015-06-01', 1),
    (01, '2015-07-01', 3),
    (01, '2015-08-01', 3),
    (01, '2015-09-01', 3),
    (02, '2015-01-01', 2),
    (02, '2015-02-01', 3),
    (02, '2015-03-01', 3),
    (02, '2015-04-01', 2),
    (02, '2015-05-01', 2),
    (02, '2015-06-01', 1),
    (02, '2015-07-01', 3),
    (02, '2015-07-02', 3),
    (02, '2015-07-03', 3)
;

Table data1 only contains the first rows where ID=1 (see SQL Fiddle).