Q

Different invoices on first and last version in group

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.

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

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close