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