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

    Unanswered: DB2 SQL Query to get the latest date only

    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)

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    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
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    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

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by asburym1 View Post
    I am using DB2 SQL queries
    no you're not, you're using SQL 2000

    http://www.dbforums.com/microsoft-sq...test-date.html

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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