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

IF the timestamp column is null, then return those null column data
```
    62046 | null | 1809
```
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
)

Nick · Accepted Answer · 2018-09-30 07:48:27Z

4

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

answered Sep 30, 2018 at 7:48

Nick

147k23 gold badges67 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Giorgos Betsos · Accepted Answer · 2018-09-30 07:22:54Z

1

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.

edited Sep 30, 2018 at 7:22

answered Sep 30, 2018 at 7:01

Giorgos Betsos

72.3k10 gold badges69 silver badges103 bronze badges

1 Comment

Hasan Hafiz Pasha Over a year ago

Thanks you sir for your answer. But may be their is a problem. In second condition, IF the timestamp column is not null, then return the latest timestamp column data 62054 | 2018-09-27 16:44:57 | 966 we need this data. But query return this, 62054 | 25.09.2018 10:18:15 | 2652

Zaynul Abadin Tuhin · Accepted Answer · 2018-09-30 08:09:26Z

1

Find out those records where time is not null and filter out timestamp nulls sample_id and for null timestamp Use union

select * from t1 where (t1.sample_id,t1.timestamp)
in (

SELECT t.sample_id,max(t.timestamp) AS time
   FROM t1 t
   WHERE t.sample_id NOT IN (select sample_id from t1 where t1.timestamp is null)
     GROUP BY t.sample_id
 )
 UNION
SELECT *
   FROM t1 t 
 WHERE t.timestamp IS NULL

output
    sample_id   timestamp           p_id
    62054       2018-09-27 16:44:57 966
    62046       null                1809

edited Sep 30, 2018 at 8:09

answered Sep 30, 2018 at 7:19

Zaynul Abadin Tuhin

32.1k6 gold badges38 silver badges67 bronze badges

Comments

Madhur Bhaiya · Accepted Answer · 2018-09-30 06:20:50Z

0

Group by on the sample_id.
Using If() function, check if the minimum value of the timestamp for the group is null or not. If it is null, return null, else return the Max() value.

Try the following query:

SELECT sample_id, 
       IF(MIN(timestamp) IS NULL, 
          NULL, 
          MAX(timestamp)) AS timestamp 
FROM your_table 
GROUP BY sample_id

edited Sep 30, 2018 at 6:20

answered Sep 30, 2018 at 6:10

Madhur Bhaiya

28.9k10 gold badges53 silver badges61 bronze badges

1 Comment

Hasan Hafiz Pasha Over a year ago

thanks your answer. But we need to filter out those data (Not on select, need it on where clause). And need the full row data. For your visualization we update the question.

Collectives™ on Stack Overflow

Mysql query to filter out data based on two logic

4 Answers 4

Comments

1 Comment

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related