From: Emi Lu <emilu@encs.concordia.ca>
To: Ozer, Pam <pozer@automotive.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Sorting Issue
Date: Mon, 09 May 2011 16:38:36 -0400
Message-ID: <4DC850CC.50409@encs.concordia.ca> (raw)
In-Reply-To: <216FFB77CBFAEE4B8EE4DF0A939FF1D14F85F8@mail-001.corp.automotive.com>
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>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: emilu@encs.concordia.ca, pozer@automotive.com
Subject: Re: Sorting Issue
In-Reply-To: <4DC850CC.50409@encs.concordia.ca>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox