Re: ranked subqueries vs distinct question - Mailing list pgsql-general

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;