James,
I reply from the web and I can't easily quote.
I don't really want to argue whether it's a workaround or not. I
understand perfectly that's valid standard sql.
However please accept that the given sql is quite complex; you have to
duplicate in the join clause the ordering...
About the complexity, practical example (simplified from my case):
CREATE TABLE temp.params (Chan INTEGER unique);
insert into params values(2);
insert into params values(4);
insert into params values(3);
insert into params values(1);
explain query plan
select count(lesser.rowid) as RANK, S.rowid, S.chan
from params as S
left outer join params as lesser
on S.chan >= lesser.chan
group by S.rowid
order by S.chan
This gives:
SCAN TABLE params AS S USING INTEGER PRIMARY KEY
SEARCH TABLE params AS lesser USING COVERING INDEX
sqlite_autoindex_params_1 (Chan<?)
USE TEMP B-TREE FOR ORDER BY
Compare to the simple usage:
explain query plan
select S.rowid, S.chan from params as S order by S.chan
returns:
SCAN TABLE params AS S USING COVERING INDEX sqlite_autoindex_params_1
I didn't even try, but AFAIK standard sql does NOT allow you to select
additional columns if they are not in group by clause (which would break
the logic), isn't it??? (though sqlite accepts it).
Of course, there would be another sql solution for this too, using
another join :) .
Given all that, I will NEVER use the pure sql (if I can use any other
solution).
Regards,
Gabriel
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
