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-ge
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 )
> o
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
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 = '0
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
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 - i
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) qu
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
au
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 (s
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
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 appr
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
>
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
2008/5/14 Karsten Hilbert <[EMAIL PROTECTED]>:
> 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:
>
>sele
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
15 matches
Mail list logo
