Re: ranked subqueries vs distinct question

On Wed, May 14, 2008 at 08:43:31AM -0500, David McNett wrote:

>> Effectively I want known-zip cities first, then
>> fragment-matching cities but without those already in the
>> known-zip list.
>
> I think you've made things far more complicated than you need.
Very likely, yes.

> How about an approach something along these lines...
>
> SELECT
> name,zip,
> (SELECT zip = '04317') as zipmatch
> FROM
> dem.urb
> ORDER BY zipmatch DESC, name;

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.

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

The full schema can be seen here:

http://salaam.homeunix.com/~ncq/gnumed/schema/devel/gnumed-schema.html

Thanks,
Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346