Does a view help you out? Are you just trying to make it easier for somebody
to create a query for that answer without typing so much?
create table v(a integer primary key,b,c,d,e,f);
insert into v values(0,NULL,NULL,2,null,9);
insert into v values(1,1,null,3,null,8);
insert into v values(2,1,null,4,4,7);
insert into v values(3,null,5,5,4,6);
insert into v values(4,1,6,6,null,5);
SELECT * FROM (SELECT b FROM v WHERE b IS NOT NULL ORDER BY a LIMIT 1),
(SELECT c FROM v WHERE c IS NOT NULL ORDER BY a LIMIT 1),
(SELECT d FROM v WHERE d IS NOT NULL ORDER BY a LIMIT 1),
(SELECT e FROM v WHERE e IS NOT NULL ORDER BY a LIMIT 1),
(SELECT f FROM v WHERE f IS NOT NULL ORDER BY a LIMIT 1);
create view vb as select b from v where b is not null order by a limit 1;
create view vc as select c from v where c is not null order by a limit 1;
create view vd as select d from v where d is not null order by a limit 1;
create view ve as select e from v where e is not null order by a limit 1;
create view vf as select f from v where f is not null order by a limit 1;
select * from vb,vc,vd,ve,vf;
.headers on
b|c|d|e|f
1|5|2|4|9
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
________________________________________
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of Steinar Midtskogen [stei...@latinitas.org]
Sent: Friday, October 19, 2012 7:04 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Find first non-NULL values of several columns
Simon Slavin <slav...@bigfraud.org> writes:
> Rows do not have an order. Without an ORDER BY clause SELECT can return rows
> in a random order if it wants. If you would like to define 'order' for me I
> can give you a SELECT which will find the first non-NULL value in a column,
> probably something like
>
> SELECT c FROM v WHERE c IS NOT NULL ORDER BY rowid LIMIT 1
Ok, so let's say the table v (with "a" as the primary key) is:
a|b|c|d|e|f
0| | |2| |9
1|1| |3| |8
2|1| |4|4|7
3| |5|5|4|6
4|1|6|6| |5
The the question becomes, is there a more convenient way to do:
SELECT * FROM (SELECT b FROM v WHERE b IS NOT NULL ORDER BY a LIMIT 1),
(SELECT c FROM v WHERE c IS NOT NULL ORDER BY a LIMIT 1),
(SELECT d FROM v WHERE d IS NOT NULL ORDER BY a LIMIT 1),
(SELECT e FROM v WHERE e IS NOT NULL ORDER BY a LIMIT 1),
(SELECT f FROM v WHERE f IS NOT NULL ORDER BY a LIMIT 1);
to get 1|5|2|4|9?
--
Steinar Midtskogen
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
