RE: Please help with a query

From: larry elkins <elkinsl_at_flash.net>
Date: Sun, 26 Nov 2000 07:50:44 -0600
Message-Id: <10692.122895@fatcity.com>

Yong,

There are a bunch of different ways to do it, and, which one is "best" really depends on the data, your indexes, and so on. Following are just two approaches of many that can be taken to avoid a huge GROUP BY clause:

select b.model, a.year
from all_honda b,

     (Select model, max(year) year
      From honda_year
      group by model) A

where a.model (+) = b.model;

select b.model, a.year
from honda_year a, all_honda b
where a.model (+) = b.model
and nvl(a.year,-1) = (Select nvl(max(year),-1)

                        From   honda_year c
                        where  c.model = a.model);

Without knowing the "real" query you are doing, anything about the data, indexes, etc., I really can't recommend any particular approach. But, the two above are just examples of many approaches that you can consider. You can even create a DB function for max(year) if you like. If neither one is adequate for your needs, provide some more info and we will see what we can do.

Regards,

Larry G. Elkins
elkinsl_at_flash.net

-----Original Message-----
From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of yong huang Sent: Friday, November 24, 2000 12:30 AM To: Multiple recipients of list ORACLE-L Subject: Please help with a query

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 Received on Sun Nov 26 2000 - 07:50:44 CST