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
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
