Skip site navigation (1) Skip section navigation (2)

Re: select records by nearest value

From: Frank Bax <fbax(at)sympatico(dot)ca>
To:
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: select records by nearest value
Date: 2011-05-14 12:27:33
Message-ID: 4DCE7535.9060908@sympatico.ca (view raw or flat)
Thread:
Lists: pgsql-novice
On 05/14/11 06:49, e-letter wrote:
> Readers,
>
> A database is structured as follows:
>
> 1 text1
> 1000 text2
> 500 text3
>
> where column 1 consists of integers. Is it possible to create query
> that will select the most appropriate records for a sum of integers.
> For example, if the target sum of integers is 50, is it possible to
> select a set of records that will be closest to this value?



I think your question is a variation of the "nearest neighbour" problem.

I found this page useful, even though I do not use PostGIS.

http://www.bostongis.com/PrinterFriendly.aspx?content_name=postgis_nearest_neighbor

In your case the "distance" function might be as simple as
	"ABS(id - 50)"
where "id" is the first column in your sample table above.

In response to

pgsql-novice by date

Next:From: Joshua TolleyDate: 2011-05-14 13:44:04
Subject: Re: More WAL archiving/backup questions
Previous:From: e-letterDate: 2011-05-14 10:49:16
Subject: select records by nearest value

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group