0

This is a sample table:

  sample_id  |      timestamp       | p_id
============================================
    62054    |  2018-09-25 10:18:15 |  2652
    62054    |  2018-09-27 16:44:57 |  966
    62046    |  null                |  1809
    62046    |  2018-09-25 10:18:15 |  2097

We need to filter out unique sample_id column, but the logic is

  1. IF the timestamp column is null, then return those null column data

        62046 | null | 1809
    
  2. IF the timestamp column is not null, then return the latest timestamp column data

     62054 | 2018-09-27 16:44:57 | 966
    

So its great if anyone provide the sql query.

We need somethings like that,

WHERE
IF(
    NOT NULL = all row group by sample_id,
    row where cancelled_at is maximum,
    null column
)

1 Answer 1

0

So we're grouping by the combination of the sample_id and wheither timestamp IS NULL. The max is returned the group. MAX(NULL) is NULL so that case is covered.

SELECT sample_id, MAX(timestamp)
FROM table
GROUP BY sample_id, timestamp IS NULL
1
  • The join back to get the p_id.
    – Rick James
    Oct 12, 2018 at 4:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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