Re: ranked subqueries vs distinct question

On May 14, 2008, at 8:15 AM, Karsten Hilbert wrote:
> Effectively I want known-zip cities first, then
> fragment-matching cities but without those already in the
> known-zip list.
>
> Can anyone teach me how I need to do this in SQL ?

I think you've made things far more complicated than you need.  How
about an approach something along these lines...

SELECT
    name,zip,
    (SELECT zip = '04317') as zipmatch
FROM
  dem.urb
ORDER BY zipmatch DESC, name;

This will give you a nice resultset incorporating a boolean field ('t'
or 'f') reflecting whether or not the zip code matches.  Sorting DESC
on that "zipmatch" field will put the trues at the top of your result
set.

No unions, no messy intersection problems.  Much faster.  I hope that
applies to your situation.

-Nugget