Re: ranked subqueries vs distinct question

2008/5/14 Karsten Hilbert <Karsten(dot)Hilbert(at)gmx(dot)net>:

> Hi all,
>
> let's assume I want to select cities by name fragment:
>
> select * from dem.urb where name ilike 'Lei%';
>
> Then, let's assume I know the zip code and want to use that
> for limiting the range of cities returned:
>
> select * from dem.urb where
> name ilike 'Lei%' and
> zip = '04317'
> ;
>
> Now, let's assume I have a widget which suggests cities
> based on the typed fragment. Another widget will already got
> the zip code and has it communicated to the city search
> field. So I want to suggest a list of cities which a) have
> the fragment and the zip code and b) have the fragment. But
> the user may have entered the wrong zip code, so include the
> cities which have just the fragment, too:
>
> select * from (
>
> select * from dem.urb where
> name ilike 'Lei%' and
> zip = '04317'
>
> union all -- avoid distinctness at this level
>
> select * from dem.urb where name ilike 'Lei%'
>
> );
>
> However, I want those ordered by name:
>
> select * from (
>
> select * from dem.urb where
> name ilike 'Lei%' and
> zip = '04317'
>
> union all -- avoid distinctness at this level
>
> select * from dem.urb where name ilike 'Lei%'
>
> )
> order by name;
>
> Then, I also want the ones with the zip code listed at the
> top of the list because they are more likely to be the ones
> (after all we already have the zip code !):
>
> select * 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%'
>
> )
> order by rank, name;
>
> This is fine. One nuisance remains: Cities which match both
> zip and name are (of course) listed twice. To eliminate
> duplicates:
>
> 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
>
> order by rank, name;
>
> This sayeth (as it should):
>
> ERROR: SELECT DISTINCT ON expressions must match initial ORDER BY
> expressions
>
> 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.
>
> Effectively I want known-zip cities first, then
> fragment-matching cities but without those already in the
> known-zip list.
>
> Can anyone teach me how I need to do this in SQL ?
>
> Do I really have to explicitely EXCEPT out the first list
> from the second sub query in the union ?
>
> Thanks,
> Karsten
> --
> GPG key ID E4071346 @ wwwkeys.pgp.net
> E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

select * from (
select *, 1 as rank from dem.urb where
name ilike 'Lei%' and
zip = '04317'
union -- avoid distinctness at this level
select *, 2 as rank from dem.urb where name ilike 'Lei%'
)
order by rank, name;

--
Kind Regards,
Mian