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:em...@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:em...@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
em...@encs.concordia.ca +1 514 848-2424 x5884
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
