On 19/10/2012 8:55 AM, Igor Tandetnik wrote:
Steinar Midtskogen <stei...@latinitas.org> wrote:
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?
If the values of b, c and so on have a known upper bound, then you can write
something like
select min(a*1000 + b), min(a*1000 + c), ..., min(a*1000 + f) from v;
Note however that your origial statement is likely more efficient, as it can
stop scanning early, whereas my variant will look at every record.
That, and it will break if those fields aren't numbers, or if any take
negative values.
I'd go for a user-defined aggregate taking two args: the key (to
identify "first") and the value to coalesce. Sure, it would never stop
the scan early, but the benefit of doing one scan instead of five
probability outweighs that (unless Steinar has an appropriate index on
every single column, which I doubt).
The python version would be:
class fnn:
def __init__(self):
self.key,self.val = None,None
def step(self, key, val):
if val is not None and (self.key is None or key < self.key):
self.key,self.val = key,val
def finalize(self):
return self.val
conn.create_aggregate('fnn', 2, fnn)
And the query would become:
select fnn(a,b), fnn(a,c), fnn(a,d), fnn(a,e), fnn(a,f) from v
Regards,
Ryan
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
