jeudi 14 juillet 2016

Max of two Columns, within found set

I have a large activity pull where I have to get the last activity of selected ca.matter_key's in a table, determined by ca.activity_key (tables primary key). I would like to know the most efficient way of getting this as the end result will be in the ballpark of 20K.

    select distinct

    ca.matter_key,
    ca.activity_key,
    ca.actrow1,
    ca.actrow2,
    ca.actrow3,
    ca.actrow4

    FROM CaseAct ca

    where
    ca.matter_key in (
    4945046,
    5040953,
    5025296,
    5078360
    )

The result from above will give the below result:

Matter_key  activity_key actrow1 actrow2 actrow3 actrow4
4945046     12           Data    Data    Data    Data    
4945046     13           Data    Data    Data    Data    
4945046     14           Data    Data    Data    Data    
5040953     35           Data    Data    Data    Data    
5040953     36           Data    Data    Data    Data    
5025296     77           Data    Data    Data    Data    
5025296     78           Data    Data    Data    Data    
5025296     79           Data    Data    Data    Data    
5078360     NULL         Data    Data    Data    Data     
4887024     93           Data    Data    Data    Data    
4887024     94           Data    Data    Data    Data    
4887024     95           Data    Data    Data    Data    
5061591     NULL         Data    Data    Data    Data    

And I need:

Matter_key  activity_key actrow1 actrow2 actrow3 actrow4 
4945046     14           Data    Data    Data    Data    
5040953     36           Data    Data    Data    Data      
5025296     79           Data    Data    Data    Data    
5078360     NULL         Data    Data    Data    Data    
4887024     95           Data    Data    Data    Data    
5061591     NULL         Data    Data    Data    Data 

I've done this on previous sets using a combination of group by ca.matter_key, and Max(ca.activity_key) using criteria, however I need the pre-selected matter_keys, which is a large list. Also, is Where in ca.activity_key() the most efficient way of pulling this list?

Thanks for any help!

Aucun commentaire:

Enregistrer un commentaire