Re: BUG #6056: sorting issues

On Mon, Jun 13, 2011 at 09:52:06PM +0000, Mike Hepworth wrote:
> I execute the following sql statment:
>
> select * from (values('HALL, ANDY'),('HALLBERG, FRANK'),('HALLDEN,
> DOUGLAS'),('HALL, DOUGLAS') ) x ORDER BY 1;
>
> and get the following results
>
> HALL, ANDY
> HALLBERG, FRANK
> HALLDEN, DOUGLAS
> HALL, DOUGLAS
> Please Help...

it would help if you would say what do you think is wrong with it.
because, the data is well sorted - prefix hall, and then, the next
sortable character is:
A
B
D
D
in 3rd and 4th string 2nd character is also taken into consideration
because 1st is the same, so it becomes:

A
B
DE
DO

so the sort order is correct.

if you'd expect to get output:
> HALL, ANDY
> HALL, DOUGLAS
> HALLBERG, FRANK
> HALLDEN, DOUGLAS

then the proper solution is to use separate fields for lastname and
firstname, and not put them both in the same column.

In Pg 9.1 you will be able to sort it the way you want using COLLATE
support.

with current situation, you can try to split every string using ", ",
and sort splitted elements separately.

Best regards,

depesz

--
The best thing about modern society is how easy it is to avoid contact with it.
http://depesz.com/