DB2 SQL Query to get the latest date only

asburym1 · #1 (**permalink**) 03-11-10, 13:08

I have a table of customers who have multiple credit scores recorded differents days. I am using DB2 SQL queries

to get the customer latest date credit score only. When I use Max(scoredate) with the customer-id and credit-score

in the group by clause, 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?

For example;

select max(scoredate) as scoredate, customer-id, credit-score
from customer-tbl
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 DDL:
CREATE TABLE customer-tbl(Col1 datetime, Col2 int, Col3 int)

sathyaram_s · #2 (**permalink**) 03-11-10, 13:14

Code:

select * from
(
select col1,col2,col3,rownumber() over (partition by col2 order by col1 desc)  rowid from customer_tbl 
) as parttab where rowid=1

Lenny77 · #3 (**permalink**) 03-11-10, 13:20

Something like this:

Code:

select t1.scoredate, t1.customer-id, t1.credit-score 
from customer-tbl t1
join
(
select max(scoredate) as scoredate, customer-id
from customer-tbl
group by customer-id) t2
on       t2.scoredate    = t1.scoredate
    and t2.customer-id = t1.customer-id

Lenny