Sorting street addresses

Поиск
Список
Период
Сортировка
От Robert Fitzpatrick
Тема Sorting street addresses
Дата
Msg-id 1098992160.30469.63.camel@columbus.webtent.org
обсуждение исходный текст
Ответы Re: Sorting street addresses  (Richard Poole <rp@guests.deus.net>)
Re: Sorting street addresses  ("Greg Sabino Mullane" <greg@turnstep.com>)
Список 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 по дате отправления:

Предыдущее
От: "Sally Sally"
Дата:
Сообщение: Re: primary key and existing unique fields
Следующее
От: "George Woodring"
Дата:
Сообщение: Issue adding foreign key