Re: Sorting Issue

> That works. Why?

http://www.postgresql.org/docs/current/static/functions-string.html

split_part(string text, delimiter text, field int) text Split string on
delimiter and return the given field (counting from one)
split_part('abc~(at)~def~@~ghi', '~(at)~', 2) def

Emi

> -----Original Message-----
> From: Emi Lu [mailto:emilu(at)encs(dot)concordia(dot)ca]
> Sent: Monday, May 09, 2011 12:38 PM
> To: Ozer, Pam
> Cc: pgsql-sql(at)postgresql(dot)org
> Subject: Re: [SQL] Sorting Issue
>
>> I have the following query
>>
>> Select VehicleTrimAbbreviated, VehicleTrimAbbreviatedId
>>
>> from VehicleTrimAbbreviated
>>
>> Where vehicleTrimAbbreviated like 'CX%'
>>
>> order by VehicleTrimAbbreviated asc
>>
>> Results:
>>
>> 532;"CX Hatchback"
>>
>> 536;"CXL Minivan"
>>
>> 3255;"CXL Premium Sedan"
>>
>> 537;"CXL Sedan"
>>
>> 538;"CXL Sport Utility"
>>
>> 3319;"CXL Turbo Sedan"
>>
>> 533;"CX Minivan"
>>
>> 1959;"CX Plus Minivan"
>>
>> 534;"CX Sedan"
>>
>> 535;"CX Sport Utility"
>>
>> 539;"CXS Sedan"
>>
>> Why would this not sort correctly? All the CX should be first, then
> CXL,
>> Then CXS
>
> Would you mind try:
>
> Select VehicleTrimAbbreviated, VehicleTrimAbbreviatedId
> from VehicleTrimAbbreviated
> Where vehicleTrimAbbreviated like 'CX%'
> order by
>
> split_part(VehicleTrimAbbreviated, ' ', 1) asc,
> split_part(VehicleTrimAbbreviated, ' ', 2) asc;
>
>