Re: ranked subqueries vs distinct question - Mailing list pgsql-general

From Karsten Hilbert
Subject Re: ranked subqueries vs distinct question
Date
Msg-id 20080514135931.GM4401@merkur.hilbert.loc
Whole thread Raw
In response to Re: ranked subqueries vs distinct question  ("mian wang" <lonelycat1984@gmail.com>)
List pgsql-general
On Wed, May 14, 2008 at 09:48:20PM +0800, mian wang wrote:

> 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;

This surely avoids duplicates but it doesn't guarantuee rank
1 matches are on top because the distinct happens *before*
the order by and thus the "surviving" rank value is AFAICT
unpredictably 1 OR 2. I want rank 1 on top and rank 2
duplicates discarded.

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

pgsql-general by date:

Previous
From: David McNett
Date:
Subject: Re: ranked subqueries vs distinct question
Next
From: Karsten Hilbert
Date:
Subject: Re: ranked subqueries vs distinct question