[SOLVED] select min(date) for set of values

Issue

How to select the min(date) for set of values like this table:

    date               status       failure_time
-------------------------------------------------
 2022-03-22 17:58:03     1             NULL
 2022-03-22 18:00:03     0             NULL
 2022-03-22 18:02:03     0             NULL
 2022-03-22 18:04:03     1              6
 2022-03-22 18:06:03     1             NULL
 2022-03-22 18:08:03     0             NULL
 2022-03-22 18:10:03     1              2

My try using this trigger :

    DECLARE LAST_STATUS integer;
    DECLARE LAST_DATE datetime;
    SET @LAST_STATUS := (SELECT `status` from crm ORDER by id DESC limit 1);
    SET @LAST_DATE   := (SELECT `date` from crm ORDER by id DESC limit 1);

    IF (NEW.status = 1 and  @LAST_STATUS = 0 ) THEN
        SET NEW.`failure_time` := TIMESTAMPDIFF(MINUTE, @LAST_DATE , NEW.date) ;
    END IF;

Gave a result like:

    date               status       failure_time
-------------------------------------------------
 2022-03-22 17:58:03     1             NULL
 2022-03-22 18:00:03     0             NULL
 2022-03-22 18:02:03     0             NULL
 2022-03-22 18:04:03     1              2
 2022-03-22 18:06:03     1             NULL
 2022-03-22 18:08:03     0             NULL
 2022-03-22 18:10:03     1              2

My guess is to create a table which works as a flag and a trigger, when there is new ‘zero’ I will insert the date inside that table, but I don’t need that work around.

I know that I need to get the date of first ‘zero’ before ‘one’ but I don’t know how.

Solution

I got the result by reading again and again my question,

DECLARE LAST_STATUS         INT;
DECLARE LAST_ONE_ID         INT;
DECLARE FIRST_ZERO_DATE     DATETIME;

select max(id) INTO LAST_ONE_ID from crm where status = 1 order by date desc;
select status INTO LAST_STATUS FROM crm ORDER BY id DESC LIMIT 1;
SELECT `date` INTO FIRST_ZERO_DATE from crm where id = ( LAST_ONE_ID + 1 ) ORDER by id DESC limit 1;

IF (NEW.status = 1 and  LAST_STATUS = 0 ) THEN
    SET NEW.`failure_time` := TIMESTAMPDIFF(SECOND, FIRST_ZERO_DATE , NEW.date) ;
END IF;

I use second for test, here’s the result:

thanks to @Bill Karwin and @barmar for your help.

also I think that there is some improvement to the previous query.

Answered By – mnsh

Answer Checked By – Clifford M. (BugsFixing Volunteer)