Results 1 to 9 of 9
  1. #1
    Join Date
    Dec 2008
    Posts
    5

    Question Unanswered: Finding max value within another column

    It seems like this should be a simple task but for some reason I'm stumped. I've tried various joins, select where IN plus some others. I can't get what I want.

    I have a table that contains account-number decimal (9) and tran-date date(4) both non-unique. For each account-number I want to return one and only one row that contains the account-number and the most recent tran-date ie. the Max(tran-date) for EACH individual account-number. I don't care about the other data in the table I just want these two columns which I will then use in a join with a different table.

    Any thoughts on how to do this?

  2. #2
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    This seems a little too simplistic to be causing you problems but..
    Code:
    SELECT account_number, MAX(tran-date)
    FROM table-name
    GROUP BY account_number
    This is will return distinct Account_numbers and the Max tran-date for each account.

    Sample data:
    Code:
    1	2009-01-01
    1	2009-01-01
    1	2009-02-02
    1	2009-03-03
    2	2009-01-01
    2	2009-03-03
    2	2009-04-04
    2	2009-04-04
    Result:
    Code:
    1 2009-03-03
    2 2009-04-04

  3. #3
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    The other way, which to my huge surprise a couple of years ago, is faster in some cases:
    SELECT account_number, tran-date
    FROM table-name A
    where ....
    and tran_date = (SELECT MAX(B.tran-date)
    FROM table-name B
    where A.account_number = B.account_number
    )
    Dave

  4. #4
    Join Date
    Feb 2009
    Posts
    114
    I would go with method #3 ... anyone?

  5. #5
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369
    Do you need to use DDL for method #3?

  6. #6
    Join Date
    Feb 2009
    Posts
    114
    Not sure. Just waiting to see how many creative solutions we can come up with for a kindergarden level SQL question

  7. #7
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369
    xaxaxa, db2dummy

  8. #8
    Join Date
    Dec 2008
    Posts
    5
    Thanks to those who answered.

    I apologize to the rest of you for insulting you with such a kindergarten question.

    I won't ask any more questions on this "helpful" forum.

  9. #9
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    RonSMeyer, I wouldn't let a few posters who only seem to supply remarks and not help stop you from using this site (or others). Most of the people are helpful and a lot of good information can be found.

    I, personally, don't find any question a problem no matter what the level as long as the person is trying to learn.

    I would point you to Graeme Birchall's site where you can download a free "DB2 SQL Cookbook' to help you learn SQL. It is located here:

    DB2 SQL Cookbook

    PS As strange as it is to me now, GROUP BY was the hardest concept I came across when learning SQL. We had a very specialized (and limited) use for DB2 and there was never any application that it could be applied to (or anyone to ask with more experience). This was either before or just at the start of the Internet expansion to the general public. I would have very much liked a site like this to ask my beginner SQL questions.

Posting Permissions

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