0

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 
  • What's the table's name? – ypercubeᵀᴹ Mar 16 '16 at 16:29
  • tblScores. the last date and score by ID I gets as follows: 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 – FilMar Mar 16 '16 at 16:43
0

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).

  • Thanks a lot. It does the job! Is there a way to get the according value other then joining it with itself on ID and max(date)? – FilMar Mar 18 '16 at 11:43
  • There is no join aside from the cross join with variables ? Are you talking of the group by ? – Julien Vavasseur Mar 18 '16 at 12:09
  • Sorry, I think I found it myself. I wanted the correct Value also but I can simply add it in the select to get the correct value (in the example 3 for both). – FilMar Mar 18 '16 at 22:03
  • I am not sure what you mean. This is basically a subquery with a group by on it. – Julien Vavasseur Mar 18 '16 at 22:04
  • It was to quick to post my comment. When I add also the value in the upper select (SELECT ID, MIN(Date), MAX(Date),value) then I get also the correct Value of the last test for each ID. Thanks for the help. – FilMar Mar 18 '16 at 22:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.