Re: [sqlite] UNION with results distinct on a particular column?
Two possible solutions:
A) pure sql ... (warning: untested) Start with all the possible col1
values, and then left join to the other tables, and pick the first
col2 found.
select col1,
coalesce( A.col2, B.col2, C.col2 ) as col2
from (
select distinct col1 from (
select col1 from A
union
select col1 from B
union
select col1 from C
) as u
) as tmp
left join A using (col1)
left join B using (col1)
left join C using (col1)
order by col1
;
B) This is potentially very easy at the application level ... here's a
perl/DBI example (also untested):
my %pairs = map {
%{ $dbh->selectall_hashref("select col1, col2 from $_", 'col1') }
} reverse qw/ A B C /;
# This next line is optional, if you want the hash values to be col2's
instead of hashrefs:
$_=$_->{col2} for values %pairs;
--david
On Tue, Mar 24, 2009 at 5:36 PM, Matthew L. Creech wrote:
> Hi,
>
> I'm hoping someone here can help me out with a query. I have multiple
> tables, each with the same schema. For example:
>
> =
> Table A:
> =
> 1|"xxx"
> 2|"yyy"
> 3|"zzz"
> =
>
> =
> Table B:
> =
> 1|"xxx222"
> 3|"zzz222"
> 5|"www"
> =
>
> I'd like a SELECT statement that yields:
>
> =
> Result:
> =
> 1|"xxx"
> 2|"yyy"
> 3|"zzz"
> 5|"www"
> =
>
> In other words, I want the UNION of all the input tables, but if there
> are multiple results that have the same value in the first column, the
> first table's value should take precedence.
>
> This seems like a common scenario, so I'm probably missing something
> trivial. :) But so far, the only way I've figured out to do this is
> with something like:
>
> SELECT * FROM
> (SELECT 1 AS precedence, col1, col2 FROM A UNION
> SELECT 2 AS precedence, col1, col2 FROM B
> ORDER BY col1 ASC, precedence DESC)
> GROUP BY precedence
> ORDER BY col1 ASC;
>
> (Just an example, I've got several other columns that have to be
> sorted on, and there can be any number of tables). This seems to do
> what I want, but it takes an order of magnitude longer than the inner
> SELECTs do on their own (i.e. without the GROUP BY which eliminates
> rows with duplicate 'col1' values). Any ideas on how I could do this
> more efficiently?
>
> Thanks!
>
> --
> Matthew L. Creech
> ___
> 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
Re: [sqlite] starting INTEGER PRIMARY KEY at 0
On Mon, Mar 16, 2009 at 10:08 AM, P Kishor wrote:
> On Mon, Mar 16, 2009 at 8:59 AM, John Machin wrote:
>> On 17/03/2009 12:33 AM, P Kishor wrote:
>>> On Mon, Mar 16, 2009 at 8:31 AM, P Kishor wrote:
is there a way to have a table start the INTEGER PRIMARY KEY sequence
at 0 (or some other arbitrary number)?
Looks like you can set it to an arbitrary number (sort of) ... this
will result in id's of 10,11,12:
CREATE TABLE test1(a INTeger primary key autoincrement, b TEXT);
select * from SQLITE_SEQUENCE;
insert into SQLITE_SEQUENCE (name, seq) VALUES ('test1', 9);
select * from SQLITE_SEQUENCE;
INSERT INTO test1(b) VALUES('hello A');
INSERT INTO test1(b) VALUES('hello B');
INSERT INTO test1(b) VALUES('hello C');
select * from SQLITE_SEQUENCE;
select * from test1;
But if you try to set the seq to 0 or -1 or -2, you always get 1,2,3
for the ids.
> dunno... only DRH can tell, but it just may be convention. My modeling
> program uses 0 as the first index, and arrays in C and Perl start at 0
> as well.
For perl (i won't speak to the c side), i'd say that the 0-based
arrays isn't a sufficient reason on it's own, for two reasons:
1) it's nice to be able to use perl "truth" and just say:
die "missing row" unless $pk;
and not have to worry about undef vs 0 (or '')
2) You probably can (should?? there are of course exceptions) code
it w/o needing the actual array index...
my $rows = $dbh->selectall_arrayref('select * from mytable', {Slice=>{}})
foreach my $row (@$rows){
warn $row->{pkCol};
warn $row->{colA};
}
(And there's also Class::DBI, or DBIx::Class, or any of the others
that nicely hide all the sql)
And a 3rd general reason (should apply to both C & Perl) -- you don't
want to be doing stuff like $myArr[ $pk ] = $row; if the pk's are
big and there are gaps, e.g. over time if rows have been deleted. Say
rows 20 -> 500 get delete'd. to store 501 you've extended the array
w/a bunch of empty/wasted elements.
--david
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Perl DBD Question
At the app level you can do something like this to check for the
"database is locked(5)" error. Note the sleep and max ~1000 attempts
functionality as well.
my $ct = 0;
while( $ct++ < 1000 ){
$dbh->do($sql, {}, @bind);
if( $dbh->err == 5 ){ # If got a locked code, try again
sleep 1;
next;
}
...
}
On Mon, Mar 16, 2009 at 7:05 AM, marcos rebelo wrote:
> Hi all
>
> I'm a Perl programmer using SQLite
>
> I want to retry to execute every command automatically, until the DB
> is not locked. In C seems that I need to set the busy_handler.
>
> How do I do this with DBD in Perl?
>
> Thanks for any help
>
> Best Regards
> Marcos Rebelo
>
> --
> Marcos Rebelo
> http://oleber.freehostia.com
> Milan Perl Mongers leader http://milan.pm.org
> ___
> 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
