Please help with a query

From: yong huang <yong321_at_yahoo.com>
Date: Thu, 23 Nov 2000 22:26:43 -0800 (PST)
Message-Id: <10689.122832@fatcity.com>

I need a query with an outer join, but in the table that could miss values (the table with (+) on its side in the WHERE clause), there may be duplicate values in this column and I only want one of them based on some condition.

E.g., the table honda_year contains 'Accord' twice. I only want to select the one with the max(YEAR), i.e. the 2nd row as shown below. But I still need to include all Honda models in all_honda even if they're not in honda_year. One way to do this is using a max(year) in the select list:

SQL> select * from honda_year;

MODEL YEAR
---------- ---------

Accord             1
Accord             2
Civic              3
Prelude            4

SQL> select * from all_honda;

MODEL

Accord
Civic
Prelude
Odyssey

SQL> select b.model, max(a.year)
  2 from honda_year a, all_honda b
  3 where a.model (+) = b.model
  4 group by b.model;

MODEL MAX(A.YEAR)
---------- -----------

Accord               2
Civic                3
Odyssey
Prelude              4

This works. But I suspect there's a better way. In fact, our real query in my work contains about 60 columns in the select list, not including the artificially added max(criterion) column; all the 60 columns have to be mentioned again in the group by clause, which looks ugly. (Actually I haven't made it work yet) I also want to avoid using minus between two bulky selects. Can anyone help? Thanks.

Yong Huang
yong321_at_yahoo.com

Do You Yahoo!?
Yahoo! Shopping - Thousands of Stores. Millions of Products. Received on Fri Nov 24 2000 - 00:26:43 CST