0

After the following query:

SELECT
    my_table.ID,
    my_table.DATETIME,
    my_table.VALUE,
    diagnostics_keys.type as type,
    diagnostics_keys.key,
    diagnostics_keys.unit,
    diagnostics_policies.type as policy,
    pair_device_voucher.device_id,
    pair_device_voucher.voucher_id
FROM
    my_table
    inner join diagnostics_keys on my_table.DIAGNOSTIC_TYPE_ID = diagnostics_keys.ID
    inner join diagnostics_policies on my_table.POLICY = diagnostics_policies.ID
    inner join pair_device_voucher on my_table.DVP_ID = pair_device_voucher.ID
WHERE
    (
        (diagnostics_keys.type = 'AVGKMKWH')
        OR (diagnostics_keys.type = 'STATUS')
    )
    AND (
        DATETIME BETWEEN 1612515739000
        AND 1612516445000
    )
ORDER BY
    DATETIME ASC

I am getting the following results as they are in the image:

enter image description here

How can I adapt the query in order to select only the rows/entries that follow after the condition type= status and value = 40 (the rows that are inside on the red rectangles)?

Or to exclude, if the status = 100/20/whatever, do not select the following entries.

These are two commands for the sample data:

CREATE TABLE my_table( 
         ID varchar(255),
         Datetime_ varchar(255),
         Value_ varchar(255),
         Type_ varchar(255),
         Key_ varchar(255),
         Unit_ varchar(255),
         Policy_ varchar(255),
         Device_ varchar(255),
         Voucher_ varchar(255) )
INSERT INTO `my_table`(
                      `ID`, 
                      `Datetime_`, 
                      `Value_`, 
                      `Type_`, 
                      `Key_`, 
                      `Unit_`, 
                      `Policy_`, 
                      `Device_`, 
                      `Voucher_`)
             VALUES ('1','1612515739000','40','STATUS','203_sts','','periodic','12345','12345'),
('2','1612515745000','327.67','AVGKMKWH','0_202_avgkmKwh','Km/Kwh
','periodic','12345','12345'),
('4','1612515754000','40','STATUS','203_sts','','periodic','12345','12345'),
('3','1612515760000','327.67','AVGKMKWH','0_202_avgkmKwh','Km/Kwh
','periodic','12345','12345'),
('56','1612515775000','327.67','AVGKMKWH','0_202_avgkmKwh','Km/Kwh
','periodic','12345','12345'),
('6','1612515775000','327.67','AVGKMKWH','0_202_avgkmKwh','Km/Kwh
','periodic','12345','12345'),
('7','1612515790000','100','STATUS','203_sts','','periodic','12345','12345'),
('8','1612515805000','327.67','AVGKMKWH','0_202_avgkmKwh','Km/Kwh
','periodic','12345','12345'),
('9','1612515813000','327.67','AVGKMKWH','0_202_avgkmKwh','Km/Kwh
','periodic','12345','12345'),
('10','1612515820000','327.67','AVGKMKWH','0_202_avgkmKwh','Km/Kwh
','periodic','12345','12345'),
('11','1612515835000','40','STATUS','203_sts','','periodic','12345','12345'),
('12','1612515850000','327.67','AVGKMKWH','0_202_avgkmKwh','Km/Kwh
','periodic','12345','12345')

Thank you in advance.

4
  • Unless Id has some important meaning (and some tightly controlled logic around it) I don't think it's appropriate to use that and DATETIME as your sort criteria. Are there unique constraints on this table? Is there a particular logic to what these rows represent (are they records for each Device_Id)? – bbaird Mar 2 at 14:43
  • The only important fields to help us with the selection are Value and Type. Maybe instead of the ID, a helpful field would be the Datetime (it is Timestamp acctualy) because it is correct and is ordered. – Adriana Ezln Mar 2 at 14:56
  • ORDER BY DATETIME ASC is used. my_table.DATETIME is not defined as unique. So nothing prevents duplicates. So ordering expression does not provide unambiguous ordering. So before/after term makes no sense. Specify unambiguous ordering expression (for example, ORDER BY DATETIME ASC, ID ASC). PS. Specify precise server version. – Akina Mar 2 at 16:11
  • Yes, need an exact Maria version for this; looks like something could be done with analytics or correlated maxing subquery to retrieve the most recent value of status rows and then only show those where the value is 40 – Caius Jard Mar 3 at 6:56
0

Perhaps something like this:

SELECT
  ...,
  (
    SELECT value 
    FROM
      (SELECT MAX(mtsub.ID) maxid FROM mytable mtsub WHERE mtsub.type_ = 'STATUS' AND mtsub.id < my.ID) maxids 
      INNER JOIN my_table mtsub2 ON mtsub2.id = maxes.maxid
  ) recent_status_value
FROM
  my_table mt ...

This should result in a column that has the most recently encountered status row's value - you can then where it for being equal to 40 in an outer query

Not a huge fan of "selects in the select list" coordination but if you don't have window functions available to help spread the most recent value of status over some rows..

Where'ing the mtsub queries to restrict their rows may help performance too; I left it out for clarity of code

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.