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