Re: [sqlite] Tricky grouping query

[luuk34]

  On 01-10-10 22:30, Igor Tandetnik wrote:
> 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
>>
>> 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.datehttp://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Tricky grouping query

[Igor Tandetnik]

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
> 
> 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

Re: [sqlite] Tricky grouping query

[Nicolas Williams]

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

[Drake Wilson]

Quoth Andy Chambers , 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

[Adam DeVita]

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 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
>
> 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