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.
