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
Select Col1, Col2, ... ColN, Max(OTDTE) as OTDTE
Group By Col1, Col2, .... ColN)
Select Col1, Col2, .... ColN, Max(OTDTE)
Group By Col1, Col2, ... ColN
Mit freundlichen Grüßen / Best regards
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
"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!"
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Gqcy
Gesendet: Tuesday, 13.8 2013 22:59
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)
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)