On Wed, 14 May 2008, Karsten Hilbert wrote: > Modifying to: > > select * from ( > > select distinct on (name) * from ( > > select *, 1 as rank from dem.urb where > name ilike 'Lei%' and > zip = '04317' > > union all -- avoid distinctness at this level > > select *, 2 as rank from dem.urb where name ilike 'Lei%' > > ) as inner_union > > ) as unique_union > > order by rank, name; > > This works. However, one nuisance remains: Because the > distinct happens before the order by rank it is happenstance > whether rank 1 cities (with zip) will be listed on top > anymore. Can't you just do something like order by name, rank as part of the distinct on subselect to force it to pick the rank 1 row for a given name? So, basically select * from ( select distinct on ... order by name, rank ) order by rank, name;
