Обсуждение: ranked subqueries vs distinct question

Hi all,

let's assume I want to select cities by name fragment:

    select * from dem.urb where name ilike 'Lei%';

Then, let's assume I know the zip code and want to use that
for limiting the range of cities returned:

    select * from dem.urb where
        name ilike 'Lei%' and
        zip = '04317'
    ;

Now, let's assume I have a widget which suggests cities
based on the typed fragment. Another widget will already got
the zip code and has it communicated to the city search
field. So I want to suggest a list of cities which a) have
the fragment and the zip code  and b) have the fragment. But
the user may have entered the wrong zip code, so include the
cities which have just the fragment, too:

    select * from (

        select * from dem.urb where
            name ilike 'Lei%' and
            zip = '04317'

        union all        -- avoid distinctness at this level

        select * from dem.urb where name ilike 'Lei%'

    );

However, I want those ordered by name:

    select * from (

        select * from dem.urb where
            name ilike 'Lei%' and
            zip = '04317'

        union all        -- avoid distinctness at this level

        select * from dem.urb where name ilike 'Lei%'

    )
    order by name;

Then, I also want the ones with the zip code listed at the
top of the list because they are more likely to be the ones
(after all we already have the zip code !):

    select * from (

        select *, 1 as rank from dem.urb where
            name ilike 'Lei%' and
            zip = '04317'

        union all        -- avoid distinctness at this level

        select *, 2 as rank from dem.urb where name ilike 'Lei%'

    )
    order by rank, name;

This is fine. One nuisance remains: Cities which match both
zip and name are (of course) listed twice. To eliminate
duplicates:

    select distinct on (name) * from (

        select *, 1 as rank from dem.urb where
            name ilike 'Lei%' and
            zip = '04317'

        union all        -- avoid distinctness at this level

        select *, 2 as rank from dem.urb where name ilike 'Lei%'

    ) as inner_union

    order by rank, name;

This sayeth (as it should):

    ERROR:  SELECT DISTINCT ON expressions must match initial ORDER BY expressions

Modifying to:

    select * from (

        select distinct on (name) * from (

            select *, 1 as rank from dem.urb where
                name ilike 'Lei%' and
                zip = '04317'

            union all        -- avoid distinctness at this level

            select *, 2 as rank from dem.urb where name ilike 'Lei%'

        ) as inner_union

    ) as unique_union

    order by rank, name;

This works. However, one nuisance remains: Because the
distinct happens before the order by rank it is happenstance
whether rank 1 cities (with zip) will be listed on top
anymore.

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 ?

Do I really have to explicitely EXCEPT out the first list
from the second sub query in the union ?

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

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

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

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

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.

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.


SELECT name, zip, zip='04317' AS zipmatch
     FROM urb LEFT JOIN streets ON (streets.urb_id = urb.urb_id )
     ORDER BY zipmatch DESC, name
;

Of course you'll need to adjust the table and field names appropriately.

Someone should probably teach the gnumed folks about schemas, too... ;)
--
Decibel!, aka Jim C. Nasby, Database Architect  decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828

On Wed, May 14, 2008 at 09:35:10AM -0500, Decibel! wrote:

> Someone should probably teach the gnumed folks about schemas, too... ;)

Why ? We use several:

    dem - demographics stuff
    clin - clinical stuff
    gm - gnumed internal stuff
    i18n - i18n-related stuff
    audit - auditing stuff
    blobs - large object (bytea/documents) related stuff
    cfg - configuration things
    ref - reference material
    au/de_de/... locale specific addons

What are your suggestions for improvement ?

Yep, there are a few things left over from the
pre-schema-PostgreSQL era which are getting cleaned up
release by release.

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

On Wed, May 14, 2008 at 09:28:50AM -0500, David McNett wrote:

> I think perhaps you have misunderstood what I was suggesting.
Very well possible.

> If the
> SQL in your original post works, then my suggestion will also work.
Indeed, my initial post had a typo. Here is the last (most complex) query as it should be:

        select * from (

                select distinct on (name) * from (

                        select *, 1 as rank from dem.v_zip2data where
                                name ilike 'Lei%' and
                                zip = '04317'

                        union all               -- avoid distinctness at this level

                        select *, 2 as rank from dem.urb where name ilike 'Lei%'

                ) as inner_union

        ) as unique_union

        order by rank, name;

Note the dem.v_zip2data in the rank 1 subquery which is a
view over those cities which do have known zip codes due to
streets (which have zip codes) linked to them.

> In
> my haste to reply I accidentally omitted the where clause of the query.
No problem, I got that.

> 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.
That conclusion is correct but my code was wrong ;-)

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

On Wed, May 14, 2008 at 09:35:10AM -0500, Decibel! wrote:

> SELECT name, zip, zip='04317' AS zipmatch
>     FROM urb LEFT JOIN streets ON (streets.urb_id = urb.urb_id )
>     ORDER BY zipmatch DESC, name
> ;

The view dem.v_zip2data (which I erronously left out in my
first post) does just that - it joins streets to urbs
thereby providing urbs with zip codes from the streets
table. It, however, only joins those rows which do have a
zip code. That leaves out those cities which don't. Which
makes me want to UNION on the dem.urb table in the initial
problem.

> Of course you'll need to adjust the table and field names appropriately.
No problem, I can do that. I am not getting the approach
right yet.

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

On Wed, May 14, 2008 at 09:35:10AM -0500, Decibel! wrote:

> Someone should probably teach the gnumed folks about schemas, too... ;)

Instead of Why? I should have said And what?  I am, of
course, open to insights on that.

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

On Wed, 14 May 2008, Karsten Hilbert wrote:

> Modifying to:
>
>     select * from (
>
>         select distinct on (name) * from (
>
>             select *, 1 as rank from dem.urb where
>                 name ilike 'Lei%' and
>                 zip = '04317'
>
>             union all        -- avoid distinctness at this level
>
>             select *, 2 as rank from dem.urb where name ilike 'Lei%'
>
>         ) as inner_union
>
>     ) as unique_union
>
>     order by rank, name;
>
> This works. However, one nuisance remains: Because the
> distinct happens before the order by rank it is happenstance
> whether rank 1 cities (with zip) will be listed on top
> anymore.

Can't you just do something like order by name, rank as part of the
distinct on subselect to force it to pick the rank 1 row for a given name?

So, basically
select * from
 ( select distinct on ... order by name, rank )
order by rank, name;

On May 14, 2008, at 9:55 AM, Karsten Hilbert wrote:
> On Wed, May 14, 2008 at 09:35:10AM -0500, Decibel! wrote:
>
>> SELECT name, zip, zip='04317' AS zipmatch
>>    FROM urb LEFT JOIN streets ON (streets.urb_id = urb.urb_id )
>>    ORDER BY zipmatch DESC, name
>> ;
>
> The view dem.v_zip2data (which I erronously left out in my
> first post) does just that - it joins streets to urbs
> thereby providing urbs with zip codes from the streets
> table. It, however, only joins those rows which do have a
> zip code. That leaves out those cities which don't. Which
> makes me want to UNION on the dem.urb table in the initial
> problem.

Doesn't the "LEFT JOIN" in decibel's suggestion account for that?
i.e. -- it isn't limited to just rows which have a zip code.
Rows in the result set with no corresponding row in the streets table
will just be represented with a NULL zip code.

If the view is limited as you describe, don't use is.  Do the LEFT
JOIN explicitly for yourself and the query should do exactly what you
wish.

Just for a lark, run that code.  I'll bet it works for you.

On Wed, May 14, 2008 at 08:21:00AM -0700, Stephan Szabo wrote:

> Can't you just do something like order by name, rank as part of the
> distinct on subselect to force it to pick the rank 1 row for a given name?
>
> So, basically
> select * from
>  ( select distinct on ... order by name, rank )
> order by rank, name;

OK, ordering twice, ..., once to bubble up by rank so the
distinct on picks the rank 1 sub-results ..., second to time
get the final order by name within the ranks. Sounds like an
idea I didn't think of. Will try that. Thanks for the
suggestion !

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

On Wed, May 14, 2008 at 10:36:31AM -0500, David McNett wrote:

> If the view is limited as you describe, don't use is.
Ah, of course, that was the best advice amongst all :-)

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