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?

This seems a little too simplistic to be causing you problems but..
This is will return distinct Account_numbers and the Max tran-date for each account.

Sample data:
Result:

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

I would go with method #3 ... anyone?

Do you need to use DDL for method #3?

Not sure. Just waiting to see how many creative solutions we can come up with for a kindergarden level SQL question

xaxaxa, db2dummy

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.

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.