-
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
-
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
-