Re: ranked subqueries vs distinct question

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