Ok, so the scenario is a database which stores customer contracts (to keep it simple) so that every time a contract is expired and renewed a new version is created in the database to reflect any changes (terms, price etc).
My page needs to display a list of the latest (current) versions of each contract, which is made really simple by the fact that the table includes a latest
flag field, so the initial query would look something like this:
select * from tblp where latest;
Now we get to the interesting part. If you take a look at the following sample dataset:
drop table if exists tblp;
create table tblp (client int,value decimal,ver int,latest tinyint,invoice char(8) null);
insert into tblp values
(1,500,1,0,'00654321'),
(1,550,2,0,'00654322'),
(1,550,3,0,'00654323'),
(1,600,4,1,null),
(2,500,1,0,'00654001'),
(2,550,2,0,'00654002'),
(2,600,3,1,'00654003');
we see that the result produced by the initial query is as follows
client value ver latest invoice
1 600 4 1 -
2 600 3 1 00654003
However I need not display entries that have not been invoiced (and are therfore not finalised). I can do this with a subquery and comparison on the version number as follows:
select * from tblp p
where (latest AND invoice IS NOT NULL)
OR (ver = (select max(pp.ver) from tblp pp where pp.client=p.client AND pp.invoice is not null))
client value ver latest invoice
1 550 3 0 00654323
2 600 3 1 00654003
However, I'm not convinced this is the best way to achieve the desired outcome. I also have performance concerns as the live dataset is quite large.
Is there anyway to grab that previous record without using a subquery?
What is the most performance efficient way of doing this?
Aslo, an execution question... will that subquery be evaluated for every row or only for rows that fail the first part of the OR condition??