CODEWITHSUNDEEP
sqlsqlite
Devolus
Devolus

Reputation: 22094

Query for first occurence of null value in SQLite

I have a table which I dynamically fill with some data I want to create some statistics for. I have one value which has some values following a certain pattern, so I created an additional column where I map the values to other values so I can group them.

Now before I run my statistics, I need to check if I have to remap these values which means that I have to check if there are null values in that column.

I can do a select like this:

select distinct 1
from my-table t
where t.status_rd is not null
;

The disadvantage is, that this returns exactly one row, but it has to perform a full select. Is there some way that I can stop the select for the first encounter? I'm not interested in the exact row, because when there is at least one row, I have to run an update on all of them anyway, but I would like to avoid running the update unnecessarily everytime.

In Oracle I would do it with rownum, but this doesn't exist in SQLite

select 1
from my-table t
where t.status_rd is not null
and rownum <= 1
;

Upvotes: 0

Views: 442

Answers (1)

lc.
lc.

Reputation: 116528

Use LIMIT 1 to select the first row returned:

SELECT 1
FROM my_table t
WHERE t.status_rd IS NULL
LIMIT 1

Note: I changed the where clause from IS NOT NULL to IS NULL based on your problem description. This may or may not be correct.

Upvotes: 4

Related Questions