Hi - I have used rank() in the past, but returning to the subject.. Assume I have a table of all schools in the US, and another with all museums, and I want to see all museums that are within some distance of each school, by school. (yes this is spatial but the distance is just a function call - no mystery there) in the following example, I used a partition by school name, and an order function of distance for ranking, WHERE reduces the results in three ways, since it is a national list. -- select distinct on (s.name) s.name as school_name, m.name as museum_name, m.admin2, st_distance( s.geom::geography, m.geom::geography )::integer as dist, rank() over ( partition by (s.name, s.admin2) order by st_distance( s.geom::geography, m.geom::geography )) as rank from museum m, school s where s.admin2 = 'Alameda County' AND m.admin1 = 'California' AND st_dwithin( m.geom::geography, s.geom::geography, 9000 ) ORDER BY s.name, dist; -- note that the DISTINCT ON() element(s) must match the ORDER BY initial items due to some internal rule in the engine.. this query seems to work.. comments welcome -- Brian M Hamlin OSGeo California Chapter blog.light42.com
