Thread: select records by nearest value
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? Thanks in advance.
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.
Thanks, I read the link and received the following error: ERROR: aggregates not allowed in WHERE clause Using the example: WHERE g1.gid = 1 and g1.gid <> g2.gid The error occurs if the function: =1 is replaced by: SUM(1000) Another question: what does '<>' mean? Couldn't find explanation in the manual.
On 05/15/2011 03:30 AM, e-letter wrote: > Thanks, I read the link and received the following error: > > ERROR: aggregates not allowed in WHERE clause > > Using the example: > > WHERE g1.gid = 1 and g1.gid<> g2.gid > > The error occurs if the function: > > =1 > > is replaced by: > > SUM(1000) That's because SUM() is an aggregate function - it collects information over a query or GROUP BY. WHERE clauses operate on values from single records. You can get the effect with a correlated subquery, perhaps. > Another question: what does '<>' mean? Couldn't find explanation in the manual. Go to the manual <http://www.postgresql.org/docs/9.0/interactive/> Click on chapter 9, "Functions and Operators". <http://www.postgresql.org/docs/9.0/interactive/functions.html> Click on chapter 9.2, "Comparison Operators". <http://www.postgresql.org/docs/9.0/interactive/functions-comparison.html> -- Lew Honi soit qui mal y pense. http://upload.wikimedia.org/wikipedia/commons/c/cf/Friz.jpg
