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

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

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

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

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

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

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/gnum...ed-schema.html

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

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.

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

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

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

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;


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

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.

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

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

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