Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2010
    Posts
    15

    Unanswered: db2 sql query to get the latest date in 2 seperate tables and combined tables

    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)
    Last edited by asburym1; 03-11-10 at 15:13.

  2. #2
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    asburym1, This is one way to get what you want for one table.
    Code:
    SELECT ScoreDate, Customer_Id, Credit_Score
    FROM CUSTOMER_TBL_1 A
    WHERE ScoreDate = (SELECT MAX(Score_Date)
                       FROM CUSTOMER_TBL_1 B
                       WHERE A.Customer_Id = B.Customer_id
                      )
    From this you should be able to get the other table and the combined table result you are looking for.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    UNION ALL two tables, then apply one of the queries in this thread.
    http://www.dbforums.com/db2/1654438-...date-only.html

    Here is an example(used sathyaram_s' answer)
    Code:
    SELECT *
      FROM (SELECT scoredate , customer_id , credit_score
                 , ROW_NUMBER()
                     OVER(PARTITION BY customer_id
                              ORDER BY scoredate DESC) rowid
              FROM (SELECT * FROM customer_tbl_1
                    UNION ALL
                    SELECT * FROM customer_tbl_2
                   ) u
           ) AS parttab
     WHERE rowid = 1
    ;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •