Re: ranked subqueries vs distinct question

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;