My table has two columns, declaration_no and invoice_no. I'd like to select declaration_no when the invoice_no on the first version of the declaration_no is not the same as on the last version of declaration_no. I should add that the declaration_no can have values like 001 for the first version, and next versions can have values 001_1, 001_2, and so on.
The design of your declaration_no is very poor. Really, the version number should be a separate column. You will find that the following query will not be very efficient because it needs to perform string functions on the declaration_no column.
select Dmin.declaration_no as min_version , Dmin.invoice_no as min_invoice , cast(substring(Dmax.declaration_no from 5 for char_length(Dmax.declaration_no)-4) as integer) as max_version , Dmax.invoice_no as max_invoice from declarations as Dmin inner join declarations as Dmax on substring(Dmax.declaration_no from 1 for 3) = Dmin.declaration_no and cast(substring(Dmax.declaration_no from 5 for char_length(Dmax.declaration_no)-4) as integer) = ( select max( cast(substring(declaration_no from 5 for char_length(Dmax.declaration_no)-4) as integer) ) from declarations where substring(declaration_no from 1 for 3) = Dmin.declaration_no ) where Dmin.invoice_no <> Dmax.invoice_no
I will leave it as an exercise for you to write the query when the version number is a separate column. It's significantly easier and more efficient.
This was first published in June 2006