Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2012
    Posts
    9

    Unanswered: Null data in Where clause of Outer join

    How would I select the max dated row from a table where null values could exist.

    Table 1
    ID Amount
    1 10
    2 20
    3 15

    Table 2
    ID Date
    1 8/5/12
    1 12/1/12
    3 8/17/11

    My desired result would be
    ID Max Date
    1 12/1/12
    2
    3 8/17/11

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    What have you tried so far?

    Andy

  3. #3
    Join Date
    Nov 2012
    Posts
    9
    Started with
    Select Table1.ID, Table2.Date
    From Table1
    LEFT OUTER JOIN Table2 on Table1.ID = Table2.ID
    Where Table2.Date = (Select Max(Table2B.Date) from Table2 Table2B Where Table2B.ID = Table2.ID)

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Quote Originally Posted by pbooker View Post
    Started with
    Select Table1.ID, Table2.Date
    From Table1
    LEFT OUTER JOIN Table2 on Table1.ID = Table2.ID
    Where Table2.Date = (Select Max(Table2B.Date) from Table2 Table2B Where Table2B.ID = Table2.ID)
    Why not:

    Code:
    Select Table1.ID, max(Table2.Date)
    From Table1 
    LEFT OUTER JOIN Table2 on Table1.ID = Table2.ID 
    group by table1.id
    Andy

  5. #5
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Thumbs up This is simple

    If you will run the this query:

    Code:
    select t1.id, t2.date
    from Table1 t1
    left join 
    (select max(date) as date, id 
      from Table2
      group by id ) t2
    on t1.id = t2.id
    You'll have exactly what you need

    Lenny

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Some other variations.

    Example 1: (using a correlated subquery.)
    Code:
    SELECT t1.id
         , t2.date
     FROM  Table1 t1
         , LATERAL
           (SELECT MAX(date) AS date
             FROM  Table2 t2
             WHERE t2.id = t1.id
           ) t2
    ;
    Example 2: (using a scalar subselect.)
    Code:
    SELECT t1.id
         , (SELECT MAX(date) AS date
             FROM  Table2 t2
             WHERE t2.id = t1.id
           )
     FROM  Table1 t1
    ;

Tags for this Thread

Posting Permissions

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