Re: ranked subqueries vs distinct question

From: Karsten Hilbert <Karsten(dot)Hilbert(at)gmx(dot)net>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: ranked subqueries vs distinct question
Date: 2008-05-14 14:50:59
Message-ID: 20080514145059.GQ4401@merkur.hilbert.loc
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Decibel! 2008-05-14 14:53:14 Re: rounding problems
Previous Message Karsten Hilbert 2008-05-14 14:45:14 Re: ranked subqueries vs distinct question