Results 1 to 3 of 3

Thread: Help with query

  1. #1
    Join Date
    Feb 2004
    Posts
    64

    Help with query

    table_A
    ol_num ap# cnt gr_dt
    -------------------------------------------
    1111 M1 040 12/31/03
    1212 M2 345 1/1/04
    2122 A1 340 11/21/03
    2126 AB 753
    3333 DB 748 1/11/04

    table_B
    file_id ol_num gr_dt
    -------------------------------
    1 1111 12/31/03
    2 1111 04/30/05
    1 1212 1/1/04
    2 2126 11/30/04
    3 2122 10/30/03
    4 2122 11/21/03
    3 3333 1/11/04

    I am looking for result as follows

    ol_num ap# cnt a.gr_dt b.gr_dt
    ---------------------------------------------------
    1111 M1 040 12/31/03 4/30/05
    1212 M2 345 1/1/04 1/1/04
    2122 A1 340 11/21/03 11/21/03
    2126 AB 753 11/30/04

    Please help. I have a query as follows.

    select max(b.file_id),a.ol_num,a.ap#,a.cnt,a.gr_dt,b.gr_d t
    from table_A a, table_B b
    where a.ol_num = b.ol_num
    and a.gr_dt <>b.gr_dt
    group by a.ol_num,a.ap#,a.cnt,a.gr_dt,b.gr_dt

  2. #2
    Join Date
    Dec 2004
    Posts
    502
    I think you wanted something like this:

    SELECT A.ol_num, A.ap#, A.cnt, A.gr_dt, B.gr_dt
    FROM table_A A JOIN table_B B ON A.ol_num = B.ol_num
    JOIN
    (SELECT ol_num, MAX(file_id) AS MaxFile_Id
    FROM table_B
    GROUP BY ol_num) AS X
    ON B.ol_num = X.ol_num AND B.file_id = X.MaxFile_Id

  3. #3
    Join Date
    Feb 2004
    Posts
    64
    Thanks. That works.

Posting Permissions

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