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.
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
and tran_date = (SELECT MAX(B.tran-date)
FROM table-name B
where A.account_number = B.account_number
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:
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.