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: [EMAIL PROTECTED]
To: [email protected]
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>
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
