Re: ranked subqueries vs distinct question

On May 14, 2008, at 9:07 AM, Karsten Hilbert wrote:
> That doesn't work, unfortunately, because the urb (cities)
> table doesn't have the zip code. That's stored in a street
> table which foreign keys into the urb table. The
> dem.v_zip2data view aggregates streets, cities, states and
> countries for which there is a know linkage to a zip code at
> the street level. IOW, there are cities for which there is
> no known zip code. I want those to be matched, too, of
> course, courtesy of the user typing part of their name.

I think perhaps you have misunderstood what I was suggesting.  If the
SQL in your original post works, then my suggestion will also work.
In my haste to reply I accidentally omitted the where clause of the
query.

Wouldn't this (full example) work?

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

If your code runs, this will too.