I'm not sure I understand exactly what's in your table; there are no
integrity constraints, so it's hard to infer anything. I added a row
to cover a case you didn't mention; maybe you think it can never
happen, but there's nothing in your schema that prevents it.
select * from history order by license, date desc, controlcode desc;
==>
License ControlCode Date
---------- ----------- ----------
Lic1 CC4 2004-01-03
Lic1 CC2 2004-01-03
Lic1 CC1 2004-01-01
Lic2 CC3 2004-02-02
Lic2 CC3 2004-02-01
> Any hints for making it at least as fast as Access?
You can't just add indices on every column. I think this does what you
want, and (to me) it's more readable:
select distinct license, controlcode, date from
(select h.license lic, h.controlcode cc, h.date dt from
(select license, max(date) d from history group by license) lmax,
history h
where h.license = lmax.license and h.date = lmax.d) lcmax,
history
where license = lcmax.lic and date < lcmax.dt and
(controlcode <> lcmax.cc or controlcode is null) ;
==>
License ControlCode Date
---------- ----------- ----------
Lic1 CC1 2004-01-01
This doesn't tell you that Lic1 was activated twice on Jan 3; for that
you need a separate query:
select license, controlcode, date from
(select h.license lic, h.date dt from
(select license, max(date) d from history group by license) lmax,
history h
where h.license = lmax.license and h.date = lmax.d
group by lic, date
having count(*) > 1) lmax2,
history
where license = lmax2.lic and date = lmax2.dt ;
==>
License ControlCode Date
---------- ----------- ----------
Lic1 CC2 2004-01-03
Lic1 CC4 2004-01-03
There's a good explanation of indices in the slide show from the PHP
conference (starting around slide 50); the link is on the main SQLite
web page. I think I'd start with one index on (license, date); delete
the others.
> While the query executes, the whole database is locked!
Yes, you really don't want somebody changing the database while you're
trying to read it. If you want to see the reason for this, do a Google
search on 'dirty read' (SQLite does not allow dirty reads). This point
is stated pretty clearly in Section 3 of www.sqlite.org/lockingv3.html.
Regards
