Re: [SQL] Sorting Issue

2011-05-25 Thread Ozer, Pam
Of Ozer, Pam Sent: Wednesday, May 18, 2011 3:22 PM To: Tom Lane Cc: Samuel Gendler; em...@encs.concordia.ca; pgsql-sql@postgresql.org Subject: Re: [SQL] Sorting Issue Is there anywhere that gives you all the available collations and their definitions? I found with the C collation it now sorts the

Re: [SQL] Sorting Issue

2011-05-18 Thread Ozer, Pam
: Tuesday, May 10, 2011 9:47 AM To: Ozer, Pam Cc: Samuel Gendler; em...@encs.concordia.ca; pgsql-sql@postgresql.org Subject: Re: [SQL] Sorting Issue "Ozer, Pam" writes: > Isn't this the English standard for collation? Or is this a non-c > locale as mentioned below? Is the

Re: [SQL] Sorting Issue

2011-05-10 Thread Scott Marlowe
On Tue, May 10, 2011 at 11:45 AM, Samuel Gendler wrote: > > > On Tue, May 10, 2011 at 9:47 AM, Tom Lane wrote: >> >> "Ozer, Pam" writes: >> > Isn't this the English standard for collation?  Or is this a non-c >> > locale as mentioned below?  Is there anyway around this? >> >> >        LC_COLLATE

Re: [SQL] Sorting Issue

2011-05-10 Thread Samuel Gendler
On Tue, May 10, 2011 at 9:47 AM, Tom Lane wrote: > "Ozer, Pam" writes: > > Isn't this the English standard for collation? Or is this a non-c > > locale as mentioned below? Is there anyway around this? > > >LC_COLLATE = 'en_US.utf8' > > en_US is probably using somebody's idea of "dictio

Re: [SQL] Sorting Issue

2011-05-10 Thread Edward W. Rouse
: pgsql-sql@postgresql.org Subject: Re: [SQL] Sorting Issue That works. Why? -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 > &g

Re: [SQL] Sorting Issue

2011-05-10 Thread Tom Lane
"Ozer, Pam" writes: > Isn't this the English standard for collation? Or is this a non-c > locale as mentioned below? Is there anyway around this? >LC_COLLATE = 'en_US.utf8' en_US is probably using somebody's idea of "dictionary order", which I believe includes ignoring spaces

Re: [SQL] Sorting Issue

2011-05-10 Thread Ozer, Pam
7;; Samuel Gendler Cc: em...@encs.concordia.ca; pgsql-sql@postgresql.org Subject: RE: [SQL] Sorting Issue The collate setting is LC_COLLATE = 'English_United States.1252' -Original Message- From: Tom Lane [mailto:t...@sss.pgh.pa.us] Sent: Monday, May 09, 201

Re: [SQL] Sorting Issue

2011-05-10 Thread Ozer, Pam
I was wrong it is LC_COLLATE = 'en_US.utf8' -Original Message- From: Ozer, Pam Sent: Monday, May 09, 2011 3:13 PM To: 'Tom Lane'; Samuel Gendler Cc: em...@encs.concordia.ca; pgsql-sql@postgresql.org Subject: RE: [SQL] Sorting Issue Th

Re: [SQL] Sorting Issue

2011-05-09 Thread Ozer, Pam
The collate setting is LC_COLLATE = 'English_United States.1252' -Original Message- From: Tom Lane [mailto:t...@sss.pgh.pa.us] Sent: Monday, May 09, 2011 2:29 PM To: Samuel Gendler Cc: em...@encs.concordia.ca; Ozer, Pam; pgsql-sql@postgresql.org Subject: Re: [SQL] Sor

Re: [SQL] Sorting Issue

2011-05-09 Thread Tom Lane
Samuel Gendler writes: > It's not at all clear why they are not coming out of the db in > alphabetically sorted order when the query includes "order by > VehicleTrimAbbreviated asc" Usually the thing to ask at this point is "what's the database's LC_COLLATE setting"? Non-C locales often have tr

Re: [SQL] Sorting Issue

2011-05-09 Thread Samuel Gendler
On Mon, May 9, 2011 at 1:38 PM, Emi Lu wrote: > Hi Pam, > > > >> Select VehicleTrimAbbreviated, VehicleTrimAbbreviatedId > >> from VehicleTrimAbbreviated > >> Where vehicleTrimAbbreviated like 'CX%' > >> order by > >> > >> split_part(VehicleTrimAbbreviated, ' ', 1) asc, > >> split_part(VehicleTri

Re: [SQL] Sorting Issue

2011-05-09 Thread Emi Lu
: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) textSpli

Re: [SQL] Sorting Issue

2011-05-09 Thread Ozer, Pam
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] Sor

Re: [SQL] Sorting Issue

2011-05-09 Thread Emi Lu
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 VehicleTr

Re: [SQL] Sorting Issue

2011-05-09 Thread Ozer, Pam
That works. Why? -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, VehicleTrimAbbr

Re: [SQL] Sorting Issue

2011-05-09 Thread Emi Lu
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