Re: SQL to retrieve min value of the combination

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.
> >

[]

When are people going to get training on solving word problems? I know High school students that could solve this.

What you do is break down the problem. I'll give some ideas and hints. Don't use the hints it you don't need them.

First step: "the nearest" office is the one with the smallest distance for that zip. You can write a query that returns that, right? (see hint #1 if needed)

Second step is to find out which particular office(s) is(are) at that distance for that zip. (see hint #2 if needed)

Lastly, remember you are working in a relational model database (see hint #3)

If you are having trouble, try writing the query, test it, then post it with a sample of its input and output data. This is like the MATH class where you teacher insisted that you SHOW THE WORK.

HTH
  Ed

Don't peak if you really don't need the hints that follow

(hint #1 your result will be two columns:zip and distance)

(hint #2 do a join of the results of the first query back to your table)

(hint #3 if hint #2 confused you, remember everything is a table, IOW, a SELECT returns a TABLE as its result.)

If you still need help, ask your "team" member that did the partition solution, "what is an in-line view?" Received on Thu Jan 15 2004 - 13:54:58 CST