Hello everyone;

I have here a code that displays the most recent date for each group of records. But the problem is, I am not able to include some fields of the table.


There are 3 tables named CUST, ACCT, and TRAN:

CUST:
CNO NAME
CN101 DAN
CN102 AAA

ACCT:
ANO CNO
AN101 CN101
AN102 CN102

TRAN:
TNO ANO TDATE BAL
TN101 AN101 01/25/2006 3,000
TN102 AN101 02/15/2006 5,000
TN103 AN102 02/01/2006 4,000
TN104 AN102 02/27/2006 8,000
TN105 AN102 03/18/2006 2,000

And the resultant table should look something like this:

ANO NAME TDATE BAL
AN101 AAA 02/15/2006 5,000
AN102 BBB 03/18/2006 2,000

Now, here's my code:

SELECT DISTINCT

B.NAME,
C.ANO,
MAX(A.TDATE)

FROM TRAN A,
CUST B,
ACCT C

WHERE B.CNO = C.CNO
AND C.ANO = A.ANO


GROUP BY B.CNO,
B.NAME,
C.ANO;

And the resultant table is:

NAME ANO MAX(TDATE)
AN101 AAA 02/15/2006
AN102 BBB 03/18/2006

The problem is, I want to add the field 'BAL' to the resultant table but when I insert 'BAL' to the 'SELECT' clause, the result will look something like this:

ANO NAME TDATE BAL
AN101 AAA 01/25/2006 3,000
AN101 AAA 02/15/2006 5,000
AN102 BBB 02/01/2006 4,000
AN102 BBB 02/27/2006 8,000
AN102 BBB 03/18/2006 2,000

I will really appreciate any help.

Thnks,
dan15ph

Sorry for taking so looong to reply. But anyway, thanks for the help r937. I finally made it. just made a couple of changes to the code. Actually, I'm still a newbie in SQL and havent used 'inner join' (just recently) and seldom in using inner queries. thanks a lot for the help.