Re: Sorting Issue

On Mon, May 9, 2011 at 1:38 PM, Emi Lu <emilu(at)encs(dot)concordia(dot)ca> wrote:

> Hi Pam,
>
>
> >> Select VehicleTrimAbbreviated, VehicleTrimAbbreviatedId
> >> from VehicleTrimAbbreviated
> >> Where vehicleTrimAbbreviated like 'CX%'
> >> order by
> >>
> >> split_part(VehicleTrimAbbreviated, ' ', 1) asc,
> >> split_part(VehicleTrimAbbreviated, ' ', 2) asc;
>
> This query works, right?
>
> Reason:
> ======
> . split_part(VehicleTrimAbbreviated, ' ', 1) return the string before the
> blank
>
> . split_part(VehicleTrimAbbreviated, ' ', 1) return the string after the
> blank
>
> So
> [1] you order by CX, CXL, CXS first
> [2] you order by second part "Hatchback, Minivan... "
>
> Is there clear now?
>
>
Not really. It should sort alphabetically the same in either case. Here's
an alphabetic sort of similar strings in python

>>> a = ["CX Hatchback", "CXL Minivan", "CXL Premium Sedan", "CXL Sedan",
"CXL Sport Utility", "CXL Turbo Sedan", "CX Minivan", "CXS Sedan"]
>>> a.sort()
>>> a
['CX Hatchback', 'CX Minivan', 'CXL Minivan', 'CXL Premium Sedan', 'CXL
Sedan', 'CXL Sport Utility', 'CXL Turbo Sedan', 'CXS Sedan']

It's not at all clear why they are not coming out of the db in
alphabetically sorted order when the query includes "order by
VehicleTrimAbbreviated asc"