Hi there...
I did found (working) solution - retrieve all rows off the database
with 'order by date asc'
and deal with it in php by overwriting $line[$id] variable with every
row. Simply as that!
It works perfectly (at least for small datasets as I do have - I
predict no more than 2k of rows)
but that's ugly as hell, so I kept digging.
>> query fields from sub-query. Giving me always error - 'l1.lineID' is
>> unknown column...
>
> Must be a limitation of SQLite v2. It should work with v3. v2 is ancient.
Probably. But what you wrote made me to check on the old (ancient :-)
docs on sqlite
and that was a bullseye! I came across c++ api docs and spotted there
that one can
(also in PHP) create an aggregate function to filter out rows.
so I went like this:
--- php code ---
function filter_unique_step(&$context,$field,$id) {
if ($context[$field] < $id) {
$context[$field] = $id;
}
}
function filter_unique_finalize(&$context) {
$context = array_values($context);
return $context[0];
}
sqlite_create_aggregate($db,'filter_unique','filter_unique_step','filter_unique_finalize');
--- /php code ---
And then query looked like this:
select * from lines
where ID in (select filter_unique(lineID,ID) as ID from lines group by lineID )
And I used ID for checking the order, since it's autoincremented index...
And then I realized that what I've just done was duplicating max()
function which led me to
the final query:
select e1.* from lines as e1, (
select lineID, max(ID) as ID from lines group by lineID
) as e2
where (e1.ID = e2.ID)
Finally result I expect!
Thanks for nudges... :-)
--
Jo
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
