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.
Dig Deeper on Oracle and SQL
Related Q&A from Rudy Limeback
Read an example of an SQL case expression from our SQL expert Rudy Limeback. Continue Reading
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clause Continue Reading
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback. Continue Reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.