Removing "extras" from table join in Oracle view - How?

From: <sehlat_at_uclink4.berkeley.edu>
Date: Thu, 22 Apr 1999 22:48:50 GMT
Message-ID: <371fa65d.29497935_at_news.supernews.com>

I'm attempting to create a view where I'm joining two tables by ID, year, and "term code". What I want the view to do is to return only one line, either a.id = b.id, a.year=b.year, a.term=b.term OR

                     a.id=b.id, b.year=MAX(b.year), b.term=MAX(b.term)

but I don't want to see *both* if the "match" id,year,term is a different year and term from the second one. I want either the row where everything matches, OR the row with the latest information, but I don't want both.

Any suggestions for how to get a view to do this? Select distinct didn't work inside a view. Received on Fri Apr 23 1999 - 00:48:50 CEST