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
