From: | Robert Fitzpatrick <robert(at)webtent(dot)com> |
---|---|
To: | PostgreSQL <pgsql-general(at)postgresql(dot)org> |
Subject: | Sorting street addresses |
Date: | 2004-10-28 19:36:00 |
Message-ID: | 1098992160.30469.63.camel@columbus.webtent.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Thanks to some help here on the list, I've been able to get addresses
sorting pretty well, but now I have a issue with same addresses on
different streets not grouping the streets. This is what I'm using a
substring search in the ORDER BY statement now like in this view:
SELECT tblhudsimilargroups.rems_id, tblhudsimilargroups.group_id,
tblhudsimilargroups.similar_group_id, tblhudbuildings.address,
tblhudbuildings.hud_building_id,
is_bldg_lbp(tblhudbuildings.hud_building_id) AS is_lbp,
is_bldg_lbp_hazard(tblhudbuildings.hud_building_id) AS is_lbp_hazard
FROM (tblhudsimilargroups LEFT JOIN tblhudbuildings ON
((tblhudsimilargroups.similar_group_id =
tblhudbuildings.similar_group_id)))
ORDER BY tblhudsimilargroups.rems_id, tblhudsimilargroups.group_id,
("substring"((tblhudbuildings.address)::text,
'[^0-9]+'::text))::character
varying, ("substring"((tblhudbuildings.address)::text,
'^[0-9]+'::text))::integer;
And getting this result:
ohc=> SELECT public.viewbldginfo.group_id, public.viewbldginfo.address
FROM public.viewbldginfo WHERE (public.viewbldginfo.rems_id
='800004136');
group_id | address
----------+--------------------------
A | 3606 ROYALTY COURT
A | 3601/3603 ROYALTY COURT
A | 3602/3604 ROYALTY COURT
A | 3605/3607 ROYALTY COURT
A | 3701/3703 MCKINLEY COURT
A | 3702/3704 MCKINLEY COURT
A | 3705/3707 MCKINLEY COURT
A | 3709/3711 MCKINLEY COURT
A | 7801/7803 SOCIAL CIRCLE
A | 7801/7803 ANDALUSIA
A | 7801/7803 HAVERSHAM
A | 7802/7804 ANDALUSIA
A | 7802/7804 HAVERSHAM
A | 7805/7807 SOCIAL CIRCLE
A | 7806/7808 HAVERSHAM
A | 7811/7813 SOCIAL CIRCLE
A | 7815/7817 SOCIAL CIRCLE
A | 7825/7827 SOCIAL CIRCLE
A | 7833/7835 SOCIAL CIRCLE
I would like all those on the same street grouped together. Is there any
tricks to getting the street names sorted first, possibly where numbers
and strings separate?
--
Robert
From | Date | Subject | |
---|---|---|---|
Next Message | George Woodring | 2004-10-28 20:14:29 | Issue adding foreign key |
Previous Message | Sally Sally | 2004-10-28 19:13:14 | Re: primary key and existing unique fields |