Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2003
    Posts
    1,074

    Unanswered: Tough SQL question

    I can ultimately go with a PL/SQL solution, but I am trying to figure this out in SQL first...

    I have 3 tables which I can combine together via primary key - foreign key relationships:

    Code:
    FROM table1
         left outer join table2 on table1.pk = table2.fk
         left outer join table3 on table1.pk = table3.fk
    For any particular table1.pk value, I need to grab the MOD_ID from one of these three tables which corresponds to the MAX(MOD_DATE) of all 3 tables. So if I had the data:

    Code:
    TABLE1
    -------
    PK  MOD_ID  MOD_DATE
    --  ------  --------
    1   AAA     1/1/2001
    2   MMM     5/10/2005
    
    
    TABLE2
    -------
    FK  MOD_ID  MOD_DATE
    --  ------  --------
    1   BBB     1/2/2001
    2   NNN     5/5/2005
    
    
    TABLE3
    -------
    FK  MOD_ID  MOD_DATE
    --  ------  --------
    1   CCC     1/3/2001
    2   OOO     5/1/2005
    So in the results I'd want to see:
    Code:
    PK  MOD_ID  MOD_DATE 
    --  ------  --------
    1   CCC     1/3/2001  
    2   MMM     5/10/2005

  2. #2
    Join Date
    Mar 2007
    Posts
    623
    The simplest way would be using CASE statement, something like
    Code:
    CASE GREATEST( table1.mod_date, table2.mod_date, table3.mod_date )
      WHEN table1.mod_date THEN table1.mod_id
      WHEN table2.mod_date THEN table2.mod_id
      WHEN table3.mod_date THEN table3.mod_id
    END
    Adjust it if you want to differently solve the situation when some dates are equal; you may also need special treating of NULL value(s).

  3. #3
    Join Date
    Dec 2003
    Posts
    1,074
    GREATEST() ... cool!

    That was working, until we realized that there were multiple child records out there, so we ended up going with

    Code:
    SELECT   PK, SUBSTR( MAX( MOD ), 15 ) AS MOD_ID
    FROM     ( SELECT PK, TO_CHAR( MOD_DATE, 'YYYYMMDDHH24MISS' ) || MOD_ID AS MOD
              FROM   TABLE1
              UNION
              SELECT FK AS PK, TO_CHAR( MOD_DATE, 'YYYYMMDDHH24MISS' ) || MOD_ID AS MOD
              FROM   TABLE2
              UNION
              SELECT FK AS PK, TO_CHAR( MOD_DATE, 'YYYYMMDDHH24MISS' ) || MOD_ID AS MOD
              FROM   TABLE3 )
    GROUP BY ORDER_TK
    --=cf

Posting Permissions

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