Message-ID: <4DC850CC.50409@encs.concordia.ca>
Date: Mon, 09 May 2011 16:38:36 -0400
From: Emi Lu <emilu@encs.concordia.ca>
Reply-To: emilu@encs.concordia.ca
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US;
	rv:1.9.1.16) Gecko/20101125 Lightning/1.0b1 Thunderbird/3.0.11
MIME-Version: 1.0
To: "Ozer, Pam" <pozer@automotive.com>
CC: pgsql-sql@postgresql.org
Subject: Re: Sorting Issue
References: 
 <216FFB77CBFAEE4B8EE4DF0A939FF1D14F85F1@mail-001.corp.automotive.com>
	<4DC84284.4000903@encs.concordia.ca>
	<216FFB77CBFAEE4B8EE4DF0A939FF1D14F85F7@mail-001.corp.automotive.com>
	<4DC845EC.1020806@encs.concordia.ca>
	<216FFB77CBFAEE4B8EE4DF0A939FF1D14F85F8@mail-001.corp.automotive.com>
In-Reply-To: 
 <216FFB77CBFAEE4B8EE4DF0A939FF1D14F85F8@mail-001.corp.automotive.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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@encs.concordia.ca]
> Sent: Monday, May 09, 2011 12:52 PM
> To: Ozer, Pam
> Cc: pgsql-sql@postgresql.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~@~def~@~ghi', '~@~', 2)	def
>
> Emi
>
>
>> -----Original Message-----
>> From: Emi Lu [mailto:emilu@encs.concordia.ca]
>> Sent: Monday, May 09, 2011 12:38 PM
>> To: Ozer, Pam
>> Cc: pgsql-sql@postgresql.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@encs.concordia.ca        +1 514 848-2424 x5884