I assume OTDTE is in both tables and all other fields are identical.
So you may merge both files with an UNION statement:
With x as (Select Col1, Col2, .... ColN, OTDTE
From Active
Union
Select Col1, Col2, ... ColN, Max(OTDTE) as OTDTE
From Closed
Group By Col1, Col2, .... ColN)
Select Col1, Col2, .... ColN, Max(OTDTE)
From x
Group By Col1, Col2, ... ColN
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Gqcy
Gesendet: Tuesday, 13.8 2013 22:59
An: midrange-l@xxxxxxxxxxxx
Betreff: SQL - return "newest" row from joined files
a order database has two tables, Active orders and Closed orders...
however the closed orders will have the same order number multiple times
(order number wraps about 2 times a year).
I have a list of order numbers, and need to return only the "newest"
order data, from either the Active file, or Closed file...
I made the following SQL, and it gets me data for the instance where the
order is no longer active:
select TTTUBID, TTSORD, COALESCE(SORD,OSORD),COALESCE(SPROD,OSPROD)
from datalib.tubtrkpf
left join datalib.activeord on SORD = TTSORD left join datalib.closedord on
OSORD = TTSORD ORDER BY OTDTE
I think I need a different type of JOIN on my closedord file (inner and
outer didn't work)
midrange.com
