Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: SQL to retrieve min value of the combination

Re: SQL to retrieve min value of the combination

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: 15 Jan 2004 13:07:28 -0800
Message-ID: <2687bb95.0401151307.78471a24@posting.google.com>


harve_projects_at_yahoo.com (HRR) wrote in message news:<53b2dd24.0401140905.4009dbf3_at_posting.google.com>...
> I am sorry for not posting the criteria. The problem is like this:
>
> I have a view with 3 columns. Columns being zip_code,local_office_id,
> distance.
> I need to find the local_office_id for each zip_code which is the
> nearest. This table has all the permutation and combination of
> zip_code,local_office_id and distance (around 600 zip codes, 100 Local
> offices).
>
> Our team was able to get the solution using first_value & partition,
> but we cannot use that in our query as the production database is
> 8.0.5
>
> Hence the result required would be
>
> 1001 2 3
> 1002 6 2
>
> For the zip_code 1001, local_office no.2 is the nearest and its 3
> miles from the center.
> For the zip_code 1002, local_office no.6 is the nearest and it is 2
> miles form the center.
>
> I hope I have made myself clear.
>
> Regards
>
> Daniel Morgan <damorgan_at_x.washington.edu> wrote in message news:<1073576716.878481_at_yasure>...
> > HRR wrote:
> >
> > > Hi,
> > >
> > > I have the following table
> > >
> > > 1001 2 3
> > > 1001 3 5
> > > 1001 5 7
> > > 1002 5 4
> > > 1002 6 2
> > >
> > > I need to get a result
> > >
> > > 1001 2 3
> > > 1002 6 2
> > >
> > > How is it possible ?
> >
> > Two thoughts. First ... this is school work and we don't do homework.
> >
> > Second ... you have not provided any information as to what the criteria
> > is for obtaining the result. Based on what you have asked the following
> > query would meet your requirement.
> >
> > SELECT *
> > FROM t
> > WHERE col1=1001
> > AND col2=2
> > AND col3=3
> > UNION
> > SELECT *
> > FROM t
> > WHERE col1=1002
> > AND col2=6
> > AND col3=2;
> >
> > Which is as useless as it seems. So post your criteria ... don't make us
> > guess ... and don't expect an answer if this is homework.

This does look like a homework assignment and in those cases I like to provide hints rather than the actual solution but here is the solution.

Break the query down. Figure out how to generate the various parts and them combine them. In this case finding the nearest location for each PO can be done using a group by. Then you can select the desired data for the row that matches the row returned in the group by.

SQL> drop table hrr;

Table dropped.

SQL> create table hrr (col1 number, col2 number, col3 number);

Table created.

SQL> insert into hrr values (1001,2,3);

1 row created.

SQL> insert into hrr values (1001,3,5);

1 row created.

SQL> insert into hrr values (1001,5,7);

1 row created.

SQL> insert into hrr values (1002,5,4);

1 row created.

SQL> insert into hrr values (1002,6,2);

1 row created.

SQL>
SQL> select a.col1, a.col2, b.col3
  2 from hrr a

  3         ,( select   c.col1, min(c.col3) as col3
  4            from     hrr c
  5            group by c.col1
  6          ) b

  7 where a.col1 = b.col1
  8 and a.col3 = b.col3
  9 /

      COL1 COL2 COL3
---------- ---------- ----------

      1001          2          3
      1002          6          2

SQL> spool off

This solution will work on any version of Oracle from 7.3 up. -- Mark D Powell -- Received on Thu Jan 15 2004 - 15:07:28 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US