On Mon, May 9, 2011 at 1:38 PM, Emi Lu
<emilu@encs.concordia.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"