get second to max

[Hearth]

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??

[smantscheff]

If there is always a second entry with the same 'ver' value as the latest, you might try:

select * from tblp
where (invoice IS NOT NULL)
order by latest desc, ver desc
limit 1,1

[Philippe.Lahaie]

im not sure about the performance of the OR in SQL , but in java and C# the single pipe "|" will test both conditions even if the first one is true, while the double pipe "||" does not, so its just a matter of finding out which model of OR SQL uses.

as far as your contract versions, where you have :

client value ver latest invoice
1 600 4 1 -
2 600 3 1 00654003

i think the solution should not be to check if the latest contract is invoiced or not, but rather to not set a contract as "latest" until its invoiced, this way, your 100% sure the show contract is complete, and its still possible for the invoicing section of your application to find the "latest non invoiced contract" without it being flagged as latest.

just my opinion anyways, if im wrong let me know!

[Biiim]

The way you did it seemed pretty much fastest. I think latest might be getting set too soon, could it be set to only set latest to 1 once it has been sent?

I just ended up with this

SELECT * FROM tblp p
WHERE ver = (SELECT MAX(ver) FROM tblp pp WHERE p.`client` = pp.client AND invoice IS NOT NULL)
GROUP BY `client`

You could even make another table that stores when an invoce has been sent - then add AND invoiceid IN (SELECT invoiceid FROM sentinvoices WHERE sent = 1 or just add sent as a coulmn in the existing table

[Biiim]

Oh i think i just thought up a good one last thing:

SELECT `client`,MAX(ver),MAX(`invoice`) FROM tblp p
WHERE invoice IS NOT NULL
GROUP BY `client`

The problem is the rest of the column values won't match up eg. the value will be of the first invoice found for the client.

Assuming the latest sent invoice will always have the highest invoice number, you can pull the invoice number too.

[Hearth]

Thanks guys, there are some great ideas here.

Biiim: I wont be able to use Group functions alone as I do need to display other data as well, and as you pointed out it will not reliably display the matching values.

Philippe: Setting the latest flag only after invoicing would be the ideal solition as a workflow change, however, this is actually set in an external system which we are importing data from, and so it is necessarily set when the new version is created regardless of status. You are correct in that I could select the latest invoiced contract regardless of this flag, as it is no longer a reliable criterea.

smantscheff: I actually hadn't thought of using the sort like that to determine both latest and max version. I will investigate using this in combination with some grouping, but I think you might be on to something.