Re: ranked subqueries vs distinct question

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