On Wed, May 14, 2008 at 09:28:50AM -0500, David McNett wrote:
> I think perhaps you have misunderstood what I was suggesting.
Very well possible.
> If the
> SQL in your original post works, then my suggestion will also work.
Indeed, my initial post had a typo. Here is the last (most complex) query as it should be:
select * from (
select distinct on (name) * from (
select *, 1 as rank from dem.v_zip2data 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;
Note the dem.v_zip2data in the rank 1 subquery which is a
view over those cities which do have known zip codes due to
streets (which have zip codes) linked to them.
> In
> my haste to reply I accidentally omitted the where clause of the query.
No problem, I got that.
> Wouldn't this (full example) work?
>
> SELECT
> name,zip,
> (SELECT zip = '04317') as zipmatch
> FROM
> dem.urb
> WHERE name ilike 'lei%'
> ORDER BY zipmatch DESC, name;
>
> If your code runs, this will too.
That conclusion is correct but my code was wrong ;-)
Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346