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