Sorting street addresses
От | Robert Fitzpatrick |
---|---|
Тема | Sorting street addresses |
Дата | |
Msg-id | 1098992160.30469.63.camel@columbus.webtent.org обсуждение исходный текст |
Ответы |
Re: Sorting street addresses
Re: Sorting street addresses |
Список | 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
В списке pgsql-general по дате отправления: