[postgis-users] Re: Compare data in one table

[Kevin Neufeld]

Another way to get the min value in a "GROUP BY" type clause, is to use 
DISTINCT ON.  The later will keep the entire first row in a set of rows, 
so it matters how the rows in the set are ordered.

http://www.postgresql.org/docs/8.3/static/sql-select.html#SQL-DISTINCT

-- This will return the entire offending row for the largest diameter 
per to_node
SELECT DISTINCT ON (to_node)
   to_node, link_name AS offended_line, diameter AS in_diameter
FROM links ORDER BY to_node, diameter DESC;

Hope that helps,
Cheers,
Kevin

Broun Uganda wrote:
> That was spot on, didn't need to change a thing
> One part is still confusing me though, I need to show the offended line 
> but when i try i get an error that i have to GROUP BY or use Aggregate 
> function
> So i decided to write like this but the min thing is an error that wont 
> always be correct.
> 
> SELECT link_name, from_node, b.link_diameter, offended_link, 
> a.in_diameter  FROM
>   (SELECT  to_node, min(link_name) AS offended_link, max(diameter) AS 
> in_diameter FROM links GROUP BY to_node) AS a,
>   (SELECT link_name, from_node, diameter AS link_diameter FROM links) AS b
> WHERE a.to_node = b.from_node AND b.link_diameter < a.in_diameter;
> 
> The target here is: Link 10 from node7 is of size12 while the previous 
> link 9 has size13 (Link 9 is one of the links that have node7 as their 
> to_node).
> 
> Broun Uganda
> 
> 
> ------------------------------------------------------------------------
> From: tekuganda at hotmail.com
> To: postgis-users at postgis.refractions.net
> Subject: Compare data in one table
> Date: Sat, 30 Aug 2008 00:23:33 +0300
> 
> I am trying to develop a trigger function that searches for lines with 
> particular properties. I have two or more lines joining at a point but 
> only one line exiting from that point. I want to check that the size of 
> the outgoing (exiting) line is always not smaller than incoming lines.
> Take such data as example:
> line_name from_point to_point size
> line1        p1              p2          2
> line2        p10             p2         3
> line3        p2            p7            1
> line4        p7            p8            3
> 
> Lines Line1 and Line2 join at point p2 (to_point), therefore line3 
> should have size at least equal to 3.
> Tried to use this select statement but doesnt work, could anyone give me 
> an idea
> Select link_name, diameter, from_node, to_node from links as ends where 
> ends.diameter< links.diameter;
> or even
> SELECT   from_node from links as dstream  intersect SELECT   to_node 
> from links as previo ;
> 
> Thank you
> 
> Broun Uganda
> 
> ------------------------------------------------------------------------
> Discover the new Windows Vista Learn more! 
> <http://search.msn.com/results.aspx?q=windows+vista&mkt=en-US&form=QBRE>
> ------------------------------------------------------------------------
> Discover the new Windows Vista Learn more! 
> <http://search.msn.com/results.aspx?q=windows+vista&mkt=en-US&form=QBRE>