ranked subqueries vs distinct question

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