Igor,
I ran your cases and came up with this after adding the (22 record to the
stack)...
sqlite> explain query plan
...>
...> select s.id, p.id
...> from stack s, stackpop p
...> where s.value = p.value
...> and s.id < p.id
...> and (select count(*)
...> from stackpop p2
...> where p2.value=p.value
...> and p2.id < p.id
...> and s.id < p.id) =
...> (select count(*)
...> from stack s2
...> where s2.value=s.value
...> and s2.id > s.id
...> and s2.id < p.id );
0|0|TABLE stack AS s
1|1|TABLE stackpop AS p USING PRIMARY KEY
0|0|TABLE stackpop AS p2 USING PRIMARY KEY
0|0|TABLE stack AS s2 USING PRIMARY KEY
Results:
2|18
6|14
9|12
Thanks again for such a clean solution...
Igor Tandetnik <[EMAIL PROTECTED]> wrote: Ken wrote:
> Does anyone have ideas on how to implement a stack using sql ????
> Given the following tables and data:
>
> create table stack( id integer primary key, value integer);
> create table stackpop ( id integer primary key, value integer );
>
> begin;
> insert into stack values (1, 1234);
> insert into stack values (2, 1234);
> insert into stack values (6, 1234);
> insert into stack values (9, 1234);
> insert into stack values (22, 1234);
>
> insert into stackpop values (12, 1234) ;
> insert into stackpop values (14, 1234) ;
> insert into stackpop values (18, 1234) ;
> commit;
>
> Do you have any ideas for a select that will return the stackpop and
> stack id's paired as follows:
> 12 | 9
> 14 | 6
> 18 | 2
What's the logic supposed to be here? Why is the stack record with id=22
omitted?
I believe I've answered this question the first time you aked it. Have
you found the answer lacking? In what respect?
Igor Tandetnik
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
Igor Tandetnik <[EMAIL PROTECTED]> wrote: Ken wrote:
> Does anyone have ideas on how to implement a stack using sql ????
> Given the following tables and data:
>
> create table stack( id integer primary key, value integer);
> create table stackpop ( id integer primary key, value integer );
>
> begin;
> insert into stack values (1, 1234);
> insert into stack values (2, 1234);
> insert into stack values (6, 1234);
> insert into stack values (9, 1234);
> insert into stack values (22, 1234);
>
> insert into stackpop values (12, 1234) ;
> insert into stackpop values (14, 1234) ;
> insert into stackpop values (18, 1234) ;
> commit;
>
> Do you have any ideas for a select that will return the stackpop and
> stack id's paired as follows:
> 12 | 9
> 14 | 6
> 18 | 2
What's the logic supposed to be here? Why is the stack record with id=22
omitted?
I believe I've answered this question the first time you aked it. Have
you found the answer lacking? In what respect?
Igor Tandetnik
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
