Mysql query to filter out data based on two logic

Question

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
)

Answer 1

This query should give you the results you want. It looks for a row with a NULL timestamp, or a row which has a non-NULL timestamp which is the maximum timestamp for that sample_id, but only if there isn't a row for that sample_id which has a NULL timestamp:

SELECT *
FROM table1 t1
WHERE timestamp IS NULL OR
      timestamp = (SELECT MAX(timestamp) 
                   FROM table1 t2 
                   WHERE t2.sample_id = t1.sample_id) AND
                   NOT EXISTS (SELECT * 
                               FROM table1 t3
                               WHERE t3.sample_id = t1.sample_id AND
                                     t3.timestamp IS NULL)

Output:

sample_id   timestamp               p_id
62054       2018-09-27T16:44:57Z    966
62046       (null)                  1809

Answer 2

Using variables:

SELECT sample_id, timestamp, p_id
FROM (
   SELECT sample_id, timestamp, p_id,
          @seq := IF(@s_id = sample_id, @seq + 1,
                     IF(@s_id := sample_id, 1, 1)) AS seq
   FROM mytable
   CROSS JOIN (SELECT @s_id := 0, @seq := 0) AS vars
   ORDER BY 
      sample_id,
      CASE 
         WHEN timestamp IS NULL THEN 1
         ELSE 2
      END,
      timestamp DESC
) AS t
WHERE t.seq = 1;

Demo

Explanation:

To understand how this works you need to execute the subquery and examine the output it produces:

SELECT sample_id, timestamp, p_id,
       @seq := IF(@s_id = sample_id, @seq + 1,
                  IF(@s_id := sample_id, 1, 1)) AS seq
FROM mytable
CROSS JOIN (SELECT @s_id := 0, @seq := 0) AS vars
ORDER BY 
   sample_id,
   CASE 
      WHEN timestamp IS NULL THEN 1
      ELSE 2
   END,
   timestamp DESC

Output:

sample_id   timestamp           p_id    seq
-------------------------------------------
62046       NULL                1809    1
62046       25.09.2018 10:18:15 2097    2
62054       27.09.2018 16:44:57 966     1
62054       25.09.2018 10:18:15 2652    2

You can see here that calculated field seq is used to prioritize records inside each sample_id slice.

Note: If you're on MySQL 8.0 you can use window functions to implement the same logic.