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)

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

This mailing list archive is Copyright 1997-2015 by MIDRANGE dot COM and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available here. If you have questions about this, please contact