CODEWITHSUNDEEP
mysqlsql
Hasan Hafiz Pasha
Hasan Hafiz Pasha

Reputation: 1432

Mysql query to filter out data based on two logic

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
)

Upvotes: 7

Views: 156

Answers (4)

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32003

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

Upvotes: 1

Nick
Nick

Reputation: 147146

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

Upvotes: 4

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

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.

Upvotes: 1

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28834

  • 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

Upvotes: 0

Related Questions