CODEWITHSUNDEEP
mysql
LaCorb
LaCorb

Reputation: 71

How to select last rows where cell is empty

I have to fetch all the rows from the end of the table where val IS NULL but only until a NOT NULL is found. From this simplified table:

+----+------+
| id | val  |
+----+------+
|  1 | 13   |
|  2 | 15   |
|  3 | NULL |
|  4 | 66   |
|  5 | NULL |
|  6 | NULL |
+----+------+

I need these rows:

+----+------+
| id | val  |
+----+------+
|  5 | NULL |
|  6 | NULL |
+----+------+

So far I didn't even find any similar question except for retrieving the last element with NULL but it's not something I'm looking for.

I appreciate any help and suggestion to look up.

Upvotes: 1

Views: 811

Answers (4)

mscdeveloper
mscdeveloper

Reputation: 2889

Maybe Like this:

SELECT id, val
FROM table_name
WHERE val IS NULL
AND 
id > (
       SELECT id
       FROM table_name
       WHERE val IS NOT NULL
       ORDER BY id DESC
       LIMIT 1
      )

Upvotes: 0

Helper
Helper

Reputation: 847

You can try this:

SELECT * FROM anyTable WHERE id > ( SELECT id FROM anyTable WHERE val IS NOT NULL ORDER BY id DESC LIMIT 1)

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521467

We can try the following approach:

SELECT id, val
FROM yourTable
WHERE id > (SELECT MIN(id) FROM yourTable t1
            WHERE NOT EXISTS (SELECT 1 FROM yourTable t2
                              WHERE t2.id > t1.id AND t2.val IS NOT NULL));

Demo

Upvotes: 2

P.Salmon
P.Salmon

Reputation: 17640

Similar to @Tim Biegeleisen , but I think testing something exists is more digestible

select * 
from t
where id > (select max(id) from t where val is not null) and    
        exists (select 1 from t where id = (select max(id) from t) and val is null);

+------+------+
| id   | val  |
+------+------+
|    5 | NULL |
|    6 | NULL |
+------+------+
2 rows in set (0.00 sec)

And with 6 having a value drop table if exists t; create table t ( id int, val int); insert into t values ( 1 , 13 ), ( 2 , 15 ), ( 3 , NULL ), ( 4 , 66 ), ( 5 , NULL ), ( 6 , 10 );

select * 
    from t
    where id > (select max(id) from t where val is not null) and    
            exists (select 1 from t where id = (select max(id) from t) and val is null);
Empty set (0.00 sec)

as expected.

Upvotes: 2

Related Questions