Re: [sqlite] Tricky grouping query
On 01-10-10 22:30, Igor Tandetnik wrote:
Andy Chambersachambers.h...@gmail.com wrote:
Given the following
create table events (
id,
date,
status
);
insert into events values ('001','a','N');
insert into events values ('001','b','N');
insert into events values ('001','c','Y');
insert into events values ('001','d','N');
insert into events values ('001','e','Y');
insert into events values ('001','f','Y');
insert into events values ('001','g','N');
Is it possible, using plain SQL, to derive the following
001,c,d
001,e,g
i.e. an N in the third column means event 001 has stopped, and a
Y means it
has started back up again. Note that because the status immediately
preceding f
is also Y, there is no corresponding row in the output
select Start.id, Start.date, Stop.date
from events Start join events Stop on (Start.id = Stop.id and Start.date
Stop.date)
where Start.status = 'Y' and Stop.status = 'N' and not exists
(select 1 from events Middle
where Middle.date Start.date and Middle.date Stop.date and
Middle.status = 'N');
Igor Tandetnik
your query returns:
001|c|d
001|e|g
001|f|g
so i tried, and come up with this:
select e.id, e.date, a.date
from events e
left join events a on e.id=a.id and e.datea.date and a.status='N'
where (select status from events p where p.date e.date order by date
desc limit 1)='N' and e.status='Y'
and (select count(*) from events where date between e.date and a.date
and status='N')=1;
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Tricky grouping query
Given the following
create table events (
id,
date,
status
);
insert into events values ('001','a','N');
insert into events values ('001','b','N');
insert into events values ('001','c','Y');
insert into events values ('001','d','N');
insert into events values ('001','e','Y');
insert into events values ('001','f','Y');
insert into events values ('001','g','N');
Is it possible, using plain SQL, to derive the following
001,c,d
001,e,g
i.e. an N in the third column means event 001 has stopped, and a
Y means it
has started back up again. Note that because the status immediately
preceding f
is also Y, there is no corresponding row in the output
Cheers,
Andy
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Tricky grouping query
Why would you want to do this in plane sql, as opposed to using the API to
go through the list and derive it?
On Fri, Oct 1, 2010 at 3:37 PM, Andy Chambers achambers.h...@gmail.comwrote:
Given the following
create table events (
id,
date,
status
);
insert into events values ('001','a','N');
insert into events values ('001','b','N');
insert into events values ('001','c','Y');
insert into events values ('001','d','N');
insert into events values ('001','e','Y');
insert into events values ('001','f','Y');
insert into events values ('001','g','N');
Is it possible, using plain SQL, to derive the following
001,c,d
001,e,g
i.e. an N in the third column means event 001 has stopped, and a
Y means it
has started back up again. Note that because the status immediately
preceding f
is also Y, there is no corresponding row in the output
Cheers,
Andy
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
--
VerifEye Technologies Inc.
905-948-0015x245
7100 Warden Ave, Unit 3
Markham ON, L3R 8B5
Canada
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Tricky grouping query
Quoth Andy Chambers achambers.h...@gmail.com, on 2010-10-01 20:37:07 +0100: Is it possible, using plain SQL, to derive the following 001,c,d 001,e,g In other words, you're comparing rows with adjacent ones? Yes, it's probably possible, but it would be very awkward and possibly slow. You'd join the table to itself or use fancy subqueries, depending on the specifics. (In particular, pairing the transitions to get the pairs back as single rows would be extra-awkward without temporary tables, I think.) Is there a reason you're trying to do this with SQL? Why not just do it in plain application logic? In the absence of more information, that would seem a more natural way to go about it. --- Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Tricky grouping query
On Fri, Oct 01, 2010 at 08:37:07PM +0100, Andy Chambers wrote:
Given the following
create table events (
id,
date,
status
);
insert into events values ('001','a','N');
insert into events values ('001','b','N');
insert into events values ('001','c','Y');
insert into events values ('001','d','N');
insert into events values ('001','e','Y');
insert into events values ('001','f','Y');
insert into events values ('001','g','N');
Is it possible, using plain SQL, to derive the following
001,c,d
001,e,g
It'd be easier, I think, if you had a monotonically increasing (always
by one) unique numeric column. The you could do a select for rows in
events where the value for that column is between two values which are
sub-queries for immediately preceding and following rows in the same
table and where the status differs from that of the row in the top-level
query.
Nico
--
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Tricky grouping query
Andy Chambers achambers.h...@gmail.com wrote:
Given the following
create table events (
id,
date,
status
);
insert into events values ('001','a','N');
insert into events values ('001','b','N');
insert into events values ('001','c','Y');
insert into events values ('001','d','N');
insert into events values ('001','e','Y');
insert into events values ('001','f','Y');
insert into events values ('001','g','N');
Is it possible, using plain SQL, to derive the following
001,c,d
001,e,g
i.e. an N in the third column means event 001 has stopped, and a
Y means it
has started back up again. Note that because the status immediately
preceding f
is also Y, there is no corresponding row in the output
select Start.id, Start.date, Stop.date
from events Start join events Stop on (Start.id = Stop.id and Start.date
Stop.date)
where Start.status = 'Y' and Stop.status = 'N' and not exists
(select 1 from events Middle
where Middle.date Start.date and Middle.date Stop.date and
Middle.status = 'N');
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
