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

from datalib.tubtrkpf
left join datalib.activeord on SORD = TTSORD left join datalib.closedord on

I think I need a different type of JOIN on my closedord file (inner and
outer didn't work)

This thread ...


Return to Archive home page | Return to MIDRANGE.COM home page