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

    Unanswered: SQL Query to get the latest date

    I have a table of customers who have multiple credit scores recorded differents days. I am using SQL 2000 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
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by asburym1 View Post
    I am using SQL 2000 queries
    no you're not, you're using DB2

    http://www.dbforums.com/db2/1654438-...date-only.html

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

  3. #3
    Join Date
    Feb 2007
    Posts
    38
    Hi

    Whatever your reason may be, in SQL Server, the answer for your question is as below. This is a question about selecting top n rows from a grouped records.

    if object_id('tempdb.#customer_tbl') is not null
    drop table #customer_tbl

    CREATE TABLE #customer_tbl(Col1 datetime, Col2 int, Col3 int)

    INSERT INTO #customer_tbl Values('2003-07-05', 100131, 715)
    INSERT INTO #customer_tbl Values('2001-03-02', 100131, 700)
    INSERT INTO #customer_tbl Values('2004-09-02', 100131, 38)
    INSERT INTO #customer_tbl Values('2005-08-02', 100131, 73)
    INSERT INTO #customer_tbl Values('2005-08-03', 100131, 78)
    INSERT INTO #customer_tbl Values('2002-01-02', 100157, 706)
    INSERT INTO #customer_tbl Values('2003-01-02', 100157, 706)
    INSERT INTO #customer_tbl Values('2004-01-02', 100157, 706)

    SELECT * FROM #customer_tbl t
    WHERE
    (
    Select Count(*)
    FROM #customer_tbl
    WHERE t.Col2=Col2
    and Col1>t.Col1
    )<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
  •