Sub-query to find latest row

[BrianTyler]

I frequently have to write queries such as:

select
a.custno,a.payref,a.payamt
from
tab a
where
a.payref =
(select
max(b.payref)
from
tab b
where
b.custno = a.custno and
b.paytype = 1)

This query should give details of the latest payment of type 1 for a particular customer.

Is there a more streamlined approach to this problem?

Regards

Brian

 

[blom0344]

select customer,amount,payref from 
(select
   a.custno as customer,
   rank() over(partition by a.custno order by a.payref   desc) as max_payref,a.payamt as amount,a.payref as payref
from tab a where a.paytype = 1) as temp
where max_payref = 1

Well, not exactly more streamlined, but I guess it is either that or a correlated subquery (you already build)




Ties Blom

 

[blom0344]

select
   a.custno,a.payref,a.payamt
from
   tab a
where
   (a.custo,a.payref) in
       (select b.custno,max(b.payref)
        from tab b
        where b.paytype = 1
        group by b.custno)

is possibly a non-correlated version


Ties Blom

 

[fredericofonseca]

First option will be the best one IF there is a index made up of
custno, paytype and payref.


And why dont you like your original SQL?

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[BrianTyler]

Thanks for your input on this.

My problem is purely on performance, as I have to extract details of the latest cash payment for over a million customers, where each customer has been paying fortnightly for several years.

My correlated sub-query will process the payments twice for each customer, and this obviously takes time. Looking at your suggestions makes me feel that a temporary table of the relevant payments from the customer possibly could be ranked more efficiently than re-accessing the base table.

I'll try Ties' ranking solution to see if timings improve.

Thanks very much for your help, as my brain still needs kicking into gear after a year of retirement.

Brian

 

[blom0344]

Hi Brian,

We thought you had really retired (you know , as in reaching a certain age )..

Nice to see you back !



Ties Blom

 

[BrianTyler]

Ties,

I did retire properly at age 62 after 40 years in the industry. A couple of months ago, my last employer phoned to say that a couple of key staff had left, and asked if I could work for a few months while replacements were brought up to speed.

I will be around until the end of April and then I'll try to retire again.

Brian

(Sorry I know that this is the wrong forum for social chit-chat)

 

[fredericofonseca]

Bryan,

The correlated query might work very well, and have a negligible effect on performance, if the customer is the key. Depending on other factors also.

If the query is accessing both the inner and outer table (which are the same on your example) on the same order, then the datapages will already be loaded on the bufferpool (because of the outer select), and hence no I/O required.

Its not always easy to figure out what is the best way to achieve a particular SQL, specially from the point of view of performance.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[MarcLodge]

Brian,
The world of DB2 does not wish to lose those members with the depth of knowledge that you have. If you have the understandable urge to re-retire come April, please keep Tek Tips as a hobby. Your input would/will (as always) be most welcome.



Regards,

Marc

 

[ddiamond]

Brian,

Here is another variation on your correlated sub-query. Don't know if it will perform better or worse than your original query, but it does avoid the use of the aggregate function max.

select
   a.custno,a.payref,a.payamt
from
   tab a
where
  not exists (
    select 1 
    from tab b
    where b.custno = a.custno and
      and b.paytype = 1
      and b.payref > a.payref)