Re: SQL to retrieve min value of the combination

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.
Received on Wed Jan 14 2004 - 11:05:07 CST