OK, I got it.
insert into seriesid
select series_id,min(ROWID) from
seriesdata group by series_id;
This gets me a table with a pointer to the firs instance of series_id
in the bigger table having multiple copies, it assumes that the ids
are contiguous, allowing me to use offset, limit to extract just the
date series I want.
Starting to get it.
On 5/6/10, Simon Davies <simon.james.dav...@googlemail.com> wrote:
> On 6 May 2010 12:03, Matt Young <youngsan...@gmail.com> wrote:
>> # I am doing a simulation of distinct
>>
>> insert into seriesid (series_id,pointer)
>> select series_id,ROWID from seriesdata as s
>> where s.series_id not in(
>> select
>> series_id
>> from
>> seriesid
>> );
>>
>> # compile the table seriesid, then in the subquery I interrodate the
>> table at its current state
>> # trying to include only members not already in. No run time erros.
>>
>>
>> Is this correct, shouyld this work?
>
> No - the subquery is not reevaluated as each row is inserted into seriesid
>
> Put unique constraint on series_id in seriesid table, then use
> insert or ignore into seriesid select series_id, ROWID from seriesdata;
>
> Regards,
> Simon
> _______________________________________________
> 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
