How to get 1 row with no null columns

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

How to get 1 row with no null columns

William Drago-2
All,

Is there a simple way to find a row in a table where none of
columns contain a null value? For example:

SELECT * FROM AnyTable WHERE (all columns IS NOT NULL) LIMIT 1;


Or do I have to do this manually in my application scanning
every column in every row until I find a row with no nulls?

Thanks,
-Bill
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to get 1 row with no null columns

Jean-Luc Hainaut
Le 12:26 11/05/2016, vous avez écrit:
>All,
>
>Is there a simple way to find a row in a table where none of columns contain a null value? For example:
>
>SELECT * FROM AnyTable WHERE (all columns IS NOT NULL) LIMIT 1;

select * from AnyTable col1||col2||...||coln is not nul limit 1;

Fine for SQLite but could fail in other RDBMS, in which numeric columns must be converted into characters: ...||cast(colj as char)|| ...

I see no way to write a generic, table-independent, query unless it is generated from the metadata of the table.

J-L Hainaut


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to get 1 row with no null columns

Kees Nuyt
In reply to this post by William Drago-2
On Wed, 11 May 2016 06:26:23 -0400, William Drago
<[hidden email]> wrote:

> All,
>
> Is there a simple way to find a row in a table where none of
> columns contain a null value? For example:
>
> SELECT * FROM AnyTable WHERE (all columns IS NOT NULL) LIMIT 1;
>
> Or do I have to do this manually in my application scanning
> every column in every row until I find a row with no nulls?

You can express your criteria in the WHERE clause, by naming all
columns:

SELECT * FROM AnyTable
WHERE col1 IS NOT NULL
  AND col3 IS NOT NULL
  AND col3 IS NOT NULL
:
: (etc)
:
LIMIT 1;

Your application could compose the WHERE clause using the output
from PRAGMA table_info(AnyTable) to iterate over all columns.

> Thanks,
> -Bill

--
Regards,

Kees Nuyt
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to get 1 row with no null columns

Simon Slavin-3
In reply to this post by William Drago-2

On 11 May 2016, at 11:26am, William Drago <[hidden email]> wrote:

> Is there a simple way to find a row in a table where none of columns contain a null value? For example:
>
> SELECT * FROM AnyTable WHERE (all columns IS NOT NULL) LIMIT 1;

The coalesce(a,b,c, ...) function returns the first of its arguments which isn't NULL.  If they're all NULL it returns NULL.  So it sort-of does what you want, but you will have to list the column names.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to get 1 row with no null columns

Scott Robison-2
I also cannot think of a way to do this without naming columns. If this is
something you have to do frequently from multiple locations, it might be
worth creating a view that does the hard work in one place.

On Wed, May 11, 2016 at 10:29 AM, Simon Slavin <[hidden email]> wrote:

>
> On 11 May 2016, at 11:26am, William Drago <[hidden email]> wrote:
>
> > Is there a simple way to find a row in a table where none of columns
> contain a null value? For example:
> >
> > SELECT * FROM AnyTable WHERE (all columns IS NOT NULL) LIMIT 1;
>
> The coalesce(a,b,c, ...) function returns the first of its arguments which
> isn't NULL.  If they're all NULL it returns NULL.  So it sort-of does what
> you want, but you will have to list the column names.
>
> Simon.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



--
Scott Robison
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users