Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2006
    Location
    Philippines
    Posts
    3

    Post Unanswered: Displaying max values of each group in SQL

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select B.NAME
         , C.ANO
         , A.TDATE
         , A.BAL
      from TRAN A
    inner
      join ACCT C
        on C.ANO = A.ANO
    inner
      join CUST B
        on C.CNO = B.CNO
     where A.TDATE
         = ( select max(TDATE)
               from TRAN
              where ANO = A.ANO )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Aug 2006
    Location
    Philippines
    Posts
    3

    Thumbs up Re:

    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.
    Last edited by dan15ph; 08-30-06 at 00:19.

Posting Permissions

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