[sqlite] How to get 1 row with no null columns

[Simon Slavin]

On 11 May 2016, at 11:26am, William Drago  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] How to get 1 row with no null columns

[Kees Nuyt]

On Wed, 11 May 2016 06:26:23 -0400, William Drago
 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] 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] How to get 1 row with no null columns

[Scott Robison]

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  wrote:

>
> On 11 May 2016, at 11:26am, William Drago  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
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Scott Robison

[sqlite] How to get 1 row with no null columns

[William Drago]

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