If you know the value of the ZIP you want to "surround" lets call it
KZIP then try
SELECT code, abs(code - kzip) as Diff from zip order by Diff Limit n;
The '-' will make MySQL convert the zip codes into numeric values, ABS
is absolute value therefore making all of the results positive, order by
puts the results in order with the smallest differences first {i.e.
closest} and the limit gives you the first n where n is the number of
results you want.
This works for 9 digit ZIP's as well as 5 digit ZIP's as long as all of
your ZIP codes have the same # of digits or you can add 0's on the end
of your 5 digit ZIP's with RPAD().
Gordon Bruce
Interstate Software
A MySQL Training & Consulting Partner
> -----Original Message-----
> From: Jan Peuker [mailto:[EMAIL PROTECTED]]
> Sent: Monday, July 22, 2002 12:54 PM
> To: Gurhan Ozen
> Cc: [EMAIL PROTECTED]
> Subject: Re: Surrounding Rows
>
> Hi,
>
> sorry for my bad earlier explanation.
> What I want to do is, select the 10(say, a value) nearest values to a
> given
> number. The easiest way would be "near 5" shows 1..10. The problem is,
the
> number should affect rows, not values, because values are not unique
nor
> in
> order. What I want to do is a query like "SELECT code FROM zip WHERE
> zip=12345 SURROUND 5,5" or "SURROUND 10". At the moment, I create a
new
> table w/ an autoincrement and do a select...insert, then I select
first
> the
> id and then id-5 and id+5. This a a) very slow and redundant b) afaik
not
> very safe.
> Thanks a lot,
>
> jan
>
>
>
> ----- Original Message -----
> From: "Gurhan Ozen" <[EMAIL PROTECTED]>
> To: "Jan Peuker" <[EMAIL PROTECTED]>;
<[EMAIL PROTECTED]>
> Sent: Monday, July 22, 2002 7:39 PM
> Subject: Re: Surrounding Rows
>
>
> > Hi,
> > Can you please explain what is the algorithm here? Say you have zip
code
> > 12345, do you need all rows thats have zipcodes between 12340 and
12350,
> and
> > vice versa?
> > We can't help without knowing what do you mean by "surrounding" .
> >
> > Gurhan
> >
> >
> > ----- Original Message -----
> > From: "Jan Peuker" <[EMAIL PROTECTED]>
> > To: <[EMAIL PROTECTED]>
> > Sent: Monday, July 22, 2002 11:03 AM
> > Subject: Surrounding Rows
> >
> >
> > > Hi list,
> > >
> > > A nut to crack:
> > > I have a medium-sized database(about 1000 rows). The keys contain
> > > zip-address-information. Now I want to get the surrounding
persons,
> e.g.
> > > someone has zip 12345 I would get(now for 4 entries):
> > > +-------+----------+
> > > | 12340 | personA |
> > > | 12345 | personB | <-- I searched for this
> > > | 12345 | personC |
> > > | 12347 | personD |
> > > +-------+----------+
> > > As you see, it's no simple arithmetical question. At the moment I
fill
> an
> > > temptable w/ ids and select then - is there another way?
> > >
> > > regards,
> > >
> > > jan
> > >
> > > it would be nice if my sql could execute a query. thank you ezlm.
> > >
> > >
> > >
---------------------------------------------------------------------
> > > Before posting, please check:
> > > http://www.mysql.com/manual.php (the manual)
> > > http://lists.mysql.com/ (the list archive)
> > >
> > > To request this thread, e-mail
<[EMAIL PROTECTED]>
> > > To unsubscribe, e-mail
> > <[EMAIL PROTECTED]>
> > > Trouble unsubscribing? Try:
http://lists.mysql.com/php/unsubscribe.php
> > >
> >
>
>
> ---------------------------------------------------------------------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail <mysql-unsubscribe-
> [EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
