Wednesday, May 14, 2008

Re: [GENERAL] ranked subqueries vs distinct question



2008/5/14 Karsten Hilbert <Karsten.Hilbert@gmx.net>:
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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


 
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;

--
Kind Regards,
Mian

No comments: