I have 2 tables of customers who have multiple credit scores recorded on differents days in both tables. I am using

SQL queries to get the customers latest date credit score only from each table. When I use Max(scoredate) with the

customer-id and credit-score in the group by clause in either table query, I still get the previous scoredates

instead of just the max scoredate. However if, I don't put the credit-score in the Select clause I get the max

scoredate. How can I get the credit-score with the max scoredate in the individual tables, then the combined tables

where customers have credit scores in both tables under different score dates?

For example;


Table 1 Query::::::::::::::::::::::::::::::::::::::::::::: :::

select max(scoredate) as scoredate, customer-id, credit-score
from customer-tbl-1
group by customer-id, credit-score

returns the following result:


scoredate customer-id credit-score
---------- ----------- ------------
2003-07-05 100131 715
2001-03-02 100131 738
2002-01-02 100157 706

I want it to return the following result:

scoredate customer-id credit-score
---------- ----------- ------------
2003-07-05 100131 715
2002-01-02 100157 706



Table 2 Query::::::::::::::::::::::::::::::::::::::::::::: :::

select max(scoredate) as scoredate, customer-id, credit-score
from customer-tbl-2
group by customer-id, credit-score

returns the following result:

scoredate customer-id credit-score
---------- ----------- ------------
2004-04-05 100275 756
2000-07-02 100275 738
2008-09-03 100157 645

I want it to return the following result:

scoredate customer-id credit-score
---------- ----------- ------------
2004-04-05 100275 756
2008-09-03 100157 645



Both Tables together should resulat as:
scoredate customer-id credit-score
---------- ----------- ------------
2003-07-05 100131 715
2008-09-03 100157 645
2004-04-05 100275 756



Table DDL:
CREATE TABLE customer-tbl-1(Col1 datetime, Col2 int, Col3 int)

CREATE TABLE customer-tbl-2(Col1 datetime, Col2 int, Col3 int)

asburym1, This is one way to get what you want for one table.
From this you should be able to get the other table and the combined table result you are looking for.

UNION ALL two tables, then apply one of the queries in this thread.
DB2 SQL Query to get the latest date only

Here is an example(used sathyaram_s' answer)