Re: Sorting Issue

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?

Emi

On 05/09/2011 03:52 PM, Ozer, Pam wrote:
> Ok but why doesn't the other way work? I can't use the function in my
> query. It is dynamically created.
>
> -----Original Message-----
> From: Emi Lu [mailto:emilu(at)encs(dot)concordia(dot)ca]
> Sent: Monday, May 09, 2011 12:52 PM
> To: Ozer, Pam
> Cc: pgsql-sql(at)postgresql(dot)org
> Subject: Re: [SQL] 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;
>>
>>

--
Emi Lu, ENCS, Concordia University, Montreal H3G 1M8
emilu(at)encs(dot)concordia(dot)ca +1 514 848-2424 x5884