Rank and Partition

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