pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSorting Issue
17+ messages / 6 participants
[nested] [flat]
* Sorting Issue
@ 2011-05-09 16:25 Ozer, Pam <pozer@automotive.com>
0 siblings, 1 reply; 17+ messages in thread
From: Ozer, Pam @ 2011-05-09 16:25 UTC (permalink / raw)
To: pgsql-sql
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
Here is the table definition:
CREATE TABLE vehicletrimabbreviated
(
vehicletrimabbreviatedid integer NOT NULL,
vehicletrimabbreviated character varying(64),
vehiclesubmodelid integer,
vehiclebodystyleid smallint,
vehiclebodystylegroupid smallint
)
WITH (
OIDS=FALSE
);
ALTER TABLE vehicletrimabbreviated OWNER TO app_user;
-- Index: vehicletrimabbreviated_i00
-- DROP INDEX vehicletrimabbreviated_i00;
CREATE UNIQUE INDEX vehicletrimabbreviated_i00
ON vehicletrimabbreviated
USING btree
(vehicletrimabbreviatedid);
-- Index: vehicletrimabbreviated_i01
-- DROP INDEX vehicletrimabbreviated_i01;
CREATE INDEX vehicletrimabbreviated_i01
ON vehicletrimabbreviated
USING btree
(lower(vehicletrimabbreviated::text));
-- Index: vehicletrimabbreviated_i02
-- DROP INDEX vehicletrimabbreviated_i02;
CREATE INDEX vehicletrimabbreviated_i02
ON vehicletrimabbreviated
USING btree
(vehiclesubmodelid, vehiclebodystyleid);
-- Index: vehicletrimabbreviated_i03
-- DROP INDEX vehicletrimabbreviated_i03;
CREATE INDEX vehicletrimabbreviated_i03
ON vehicletrimabbreviated
USING btree
(vehiclebodystyleid);
-- Index: vehicletrimabbreviated_i04
-- DROP INDEX vehicletrimabbreviated_i04;
CREATE INDEX vehicletrimabbreviated_i04
ON vehicletrimabbreviated
USING btree
(vehiclebodystylegroupid, vehiclesubmodelid, vehiclebodystyleid);
-- Index: vehicletrimabbreviated_i05
-- DROP INDEX vehicletrimabbreviated_i05;
CREATE INDEX vehicletrimabbreviated_i05
ON vehicletrimabbreviated
USING btree
(lower(vehicletrimabbreviated::text), vehiclesubmodelid);
-- Index: vehicletrimabbreviated_i06
-- DROP INDEX vehicletrimabbreviated_i06;
CREATE INDEX vehicletrimabbreviated_i06
ON vehicletrimabbreviated
USING btree
(vehiclesubmodelid, vehiclebodystyleid);
Pam Ozer
Data Architect
pozer@automotive.com <mailto:pozer@automotive.com>
tel. 949.705.3468
Source Interlink Media
1733 Alton Pkwy Suite 100, Irvine, CA 92606
www.simautomotive.com <http://www.simautomotive.com;
Confidentiality Notice- This electronic communication, and all
information herein, including files attached hereto, is private, and is
the property of the sender. This communication is intended only for the
use of the individual or entity named above. If you are not the intended
recipient, you are hereby notified that any disclosure of; dissemination
of; distribution of; copying of; or, taking any action in reliance upon
this communication, is strictly prohibited. If you have received this
communication in error, please immediately notify us by telephone,
(949)-705-3000, and destroy all copies of this communication. Thank you.
Attachments:
[image/jpeg] image001.jpg (3.7K, ../../216FFB77CBFAEE4B8EE4DF0A939FF1D14F85F1@mail-001.corp.automotive.com/3-image001.jpg)
download | view image
[image/jpeg] image002.jpg (346B, ../../216FFB77CBFAEE4B8EE4DF0A939FF1D14F85F1@mail-001.corp.automotive.com/4-image002.jpg)
download | view image
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Sorting Issue
@ 2011-05-09 19:37 Emi Lu <emilu@encs.concordia.ca>
parent: Ozer, Pam <pozer@automotive.com>
0 siblings, 1 reply; 17+ messages in thread
From: Emi Lu @ 2011-05-09 19:37 UTC (permalink / raw)
To: Ozer, Pam <pozer@automotive.com>; +Cc: pgsql-sql
> 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;
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Sorting Issue
@ 2011-05-09 19:39 Ozer, Pam <pozer@automotive.com>
parent: Emi Lu <emilu@encs.concordia.ca>
0 siblings, 2 replies; 17+ messages in thread
From: Ozer, Pam @ 2011-05-09 19:39 UTC (permalink / raw)
To: emilu@encs.concordia.ca; +Cc: pgsql-sql
That works. Why?
-----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;
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Sorting Issue
@ 2011-05-09 19:52 Emi Lu <emilu@encs.concordia.ca>
parent: Ozer, Pam <pozer@automotive.com>
1 sibling, 1 reply; 17+ messages in thread
From: Emi Lu @ 2011-05-09 19:52 UTC (permalink / raw)
To: Ozer, Pam <pozer@automotive.com>; +Cc: pgsql-sql
> 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;
>
>
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Sorting Issue
@ 2011-05-09 19:52 Ozer, Pam <pozer@automotive.com>
parent: Emi Lu <emilu@encs.concordia.ca>
0 siblings, 1 reply; 17+ messages in thread
From: Ozer, Pam @ 2011-05-09 19:52 UTC (permalink / raw)
To: emilu@encs.concordia.ca; +Cc: pgsql-sql
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;
>
>
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Sorting Issue
@ 2011-05-09 20:38 Emi Lu <emilu@encs.concordia.ca>
parent: Ozer, Pam <pozer@automotive.com>
0 siblings, 1 reply; 17+ messages in thread
From: Emi Lu @ 2011-05-09 20:38 UTC (permalink / raw)
To: Ozer, Pam <pozer@automotive.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Sorting Issue
@ 2011-05-09 21:08 Samuel Gendler <sgendler@ideasculptor.com>
parent: Emi Lu <emilu@encs.concordia.ca>
0 siblings, 1 reply; 17+ messages in thread
From: Samuel Gendler @ 2011-05-09 21:08 UTC (permalink / raw)
To: emilu@encs.concordia.ca; +Cc: Ozer, Pam <pozer@automotive.com>; pgsql-sql
On Mon, May 9, 2011 at 1:38 PM, Emi Lu <emilu@encs.concordia.ca> wrote:
> 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?
>
>
Not really. It should sort alphabetically the same in either case. Here's
an alphabetic sort of similar strings in python
>>> a = ["CX Hatchback", "CXL Minivan", "CXL Premium Sedan", "CXL Sedan",
"CXL Sport Utility", "CXL Turbo Sedan", "CX Minivan", "CXS Sedan"]
>>> a.sort()
>>> a
['CX Hatchback', 'CX Minivan', 'CXL Minivan', 'CXL Premium Sedan', 'CXL
Sedan', 'CXL Sport Utility', 'CXL Turbo Sedan', 'CXS Sedan']
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"
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Sorting Issue
@ 2011-05-09 21:28 Tom Lane <tgl@sss.pgh.pa.us>
parent: Samuel Gendler <sgendler@ideasculptor.com>
0 siblings, 1 reply; 17+ messages in thread
From: Tom Lane @ 2011-05-09 21:28 UTC (permalink / raw)
To: Samuel Gendler <sgendler@ideasculptor.com>; +Cc: emilu@encs.concordia.ca, "Ozer, Pam" <pozer@automotive.com>; pgsql-sql
Samuel Gendler <sgendler@ideasculptor.com> 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 truly bizarre
sorting rules.
regards, tom lane
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Sorting Issue
@ 2011-05-09 22:13 Ozer, Pam <pozer@automotive.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 17+ messages in thread
From: Ozer, Pam @ 2011-05-09 22:13 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; Samuel Gendler <sgendler@ideasculptor.com>; +Cc: emilu@encs.concordia.ca; pgsql-sql
The collate setting is
LC_COLLATE = 'English_United States.1252'
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Monday, May 09, 2011 2:29 PM
To: Samuel Gendler
Cc: emilu@encs.concordia.ca; Ozer, Pam; pgsql-sql@postgresql.org
Subject: Re: [SQL] Sorting Issue
Samuel Gendler <sgendler@ideasculptor.com> 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 truly bizarre
sorting rules.
regards, tom lane
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Sorting Issue
@ 2011-05-10 15:07 Ozer, Pam <pozer@automotive.com>
parent: Ozer, Pam <pozer@automotive.com>
0 siblings, 1 reply; 17+ messages in thread
From: Ozer, Pam @ 2011-05-10 15:07 UTC (permalink / raw)
To: Ozer, Pam <pozer@automotive.com>; Tom Lane <tgl@sss.pgh.pa.us>; Samuel Gendler <sgendler@ideasculptor.com>; +Cc: emilu@encs.concordia.ca; pgsql-sql
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: emilu@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:tgl@sss.pgh.pa.us]
Sent: Monday, May 09, 2011 2:29 PM
To: Samuel Gendler
Cc: emilu@encs.concordia.ca; Ozer, Pam; pgsql-sql@postgresql.org
Subject: Re: [SQL] Sorting Issue
Samuel Gendler <sgendler@ideasculptor.com> 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 truly bizarre
sorting rules.
regards, tom lane
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Sorting Issue
@ 2011-05-10 15:26 Ozer, Pam <pozer@automotive.com>
parent: Ozer, Pam <pozer@automotive.com>
0 siblings, 1 reply; 17+ messages in thread
From: Ozer, Pam @ 2011-05-10 15:26 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; Samuel Gendler <sgendler@ideasculptor.com>; +Cc: emilu@encs.concordia.ca; pgsql-sql
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'
Thanks
Pam
-----Original Message-----
From: Ozer, Pam
Sent: Monday, May 09, 2011 3:13 PM
To: 'Tom Lane'; Samuel Gendler
Cc: emilu@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:tgl@sss.pgh.pa.us]
Sent: Monday, May 09, 2011 2:29 PM
To: Samuel Gendler
Cc: emilu@encs.concordia.ca; Ozer, Pam; pgsql-sql@postgresql.org
Subject: Re: [SQL] Sorting Issue
Samuel Gendler <sgendler@ideasculptor.com> 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 truly bizarre
sorting rules.
regards, tom lane
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Sorting Issue
@ 2011-05-10 16:47 Tom Lane <tgl@sss.pgh.pa.us>
parent: Ozer, Pam <pozer@automotive.com>
0 siblings, 2 replies; 17+ messages in thread
From: Tom Lane @ 2011-05-10 16:47 UTC (permalink / raw)
To: Ozer, Pam <pozer@automotive.com>; +Cc: Samuel Gendler <sgendler@ideasculptor.com>; emilu@encs.concordia.ca; pgsql-sql
"Ozer, Pam" <pozer@automotive.com> 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 in the first pass. You might be
happier using "C" collation. Unfortunately that requires re-initdb'ing
your database (as of existing PG releases).
regards, tom lane
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Sorting Issue
@ 2011-05-10 16:57 Edward W. Rouse <erouse@comsquared.com>
parent: Ozer, Pam <pozer@automotive.com>
1 sibling, 0 replies; 17+ messages in thread
From: Edward W. Rouse @ 2011-05-10 16:57 UTC (permalink / raw)
To: pgsql-sql
Looks like the sort is removing the spaces before sorting.
cxh
cxlm
cxlp
etc...
Edward W. Rouse
-----Original Message-----
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]
On Behalf Of Ozer, Pam
Sent: Monday, May 09, 2011 3:39 PM
To: emilu@encs.concordia.ca
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] Sorting Issue
That works. Why?
-----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;
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Sorting Issue
@ 2011-05-10 17:45 Samuel Gendler <sgendler@ideasculptor.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 17+ messages in thread
From: Samuel Gendler @ 2011-05-10 17:45 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Ozer, Pam <pozer@automotive.com>; emilu@encs.concordia.ca; pgsql-sql
On Tue, May 10, 2011 at 9:47 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Ozer, Pam" <pozer@automotive.com> 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 in the first pass. You might be
> happier using "C" collation. Unfortunately that requires re-initdb'ing
> your database (as of existing PG releases).
>
ugh. So what's the initdb incantation necessary to sort the way I'd expect
an alphabetic sort to happen? I'm literally just in the process of bringing
up a new project, so it's a perfect opportunity for me to get this set up
correctly to begin with. THe default on my system was definitely
en_US.utf8.
--sam
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Sorting Issue
@ 2011-05-10 17:56 Scott Marlowe <scott.marlowe@gmail.com>
parent: Samuel Gendler <sgendler@ideasculptor.com>
0 siblings, 0 replies; 17+ messages in thread
From: Scott Marlowe @ 2011-05-10 17:56 UTC (permalink / raw)
To: Samuel Gendler <sgendler@ideasculptor.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Ozer, Pam <pozer@automotive.com>; emilu@encs.concordia.ca; pgsql-sql
On Tue, May 10, 2011 at 11:45 AM, Samuel Gendler
<sgendler@ideasculptor.com> wrote:
>
>
> On Tue, May 10, 2011 at 9:47 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>
>> "Ozer, Pam" <pozer@automotive.com> 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 in the first pass. You might be
>> happier using "C" collation. Unfortunately that requires re-initdb'ing
>> your database (as of existing PG releases).
>
>
> ugh. So what's the initdb incantation necessary to sort the way I'd expect
> an alphabetic sort to happen? I'm literally just in the process of bringing
> up a new project, so it's a perfect opportunity for me to get this set up
> correctly to begin with. THe default on my system was definitely
> en_US.utf8.
initdb --locale=C
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Sorting Issue
@ 2011-05-18 22:22 Ozer, Pam <pozer@automotive.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 17+ messages in thread
From: Ozer, Pam @ 2011-05-18 22:22 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Samuel Gendler <sgendler@ideasculptor.com>; emilu@encs.concordia.ca; pgsql-sql
Is there anywhere that gives you all the available collations and their
definitions? I found with the C collation it now sorts the spaces
correct but it is also case sensitive which messes with some of our
other sorts.
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Tuesday, May 10, 2011 9:47 AM
To: Ozer, Pam
Cc: Samuel Gendler; emilu@encs.concordia.ca; pgsql-sql@postgresql.org
Subject: Re: [SQL] Sorting Issue
"Ozer, Pam" <pozer@automotive.com> 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 in the first pass. You might be
happier using "C" collation. Unfortunately that requires re-initdb'ing
your database (as of existing PG releases).
regards, tom lane
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Sorting Issue
@ 2011-05-25 22:18 Ozer, Pam <pozer@automotive.com>
parent: Ozer, Pam <pozer@automotive.com>
0 siblings, 0 replies; 17+ messages in thread
From: Ozer, Pam @ 2011-05-25 22:18 UTC (permalink / raw)
To: Ozer, Pam <pozer@automotive.com>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Samuel Gendler <sgendler@ideasculptor.com>; emilu@encs.concordia.ca; pgsql-sql
Since no one has responded does that mean there is no list anywhere? Or
does anyone know of a collation that will allow for case insensitive
sorting as well as not ignoring spaces?
-----Original Message-----
From: pgsql-sql-owner@postgresql.org
[mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Ozer, Pam
Sent: Wednesday, May 18, 2011 3:22 PM
To: Tom Lane
Cc: Samuel Gendler; emilu@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 spaces
correct but it is also case sensitive which messes with some of our
other sorts.
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Tuesday, May 10, 2011 9:47 AM
To: Ozer, Pam
Cc: Samuel Gendler; emilu@encs.concordia.ca; pgsql-sql@postgresql.org
Subject: Re: [SQL] Sorting Issue
"Ozer, Pam" <pozer@automotive.com> 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 in the first pass. You might be
happier using "C" collation. Unfortunately that requires re-initdb'ing
your database (as of existing PG releases).
regards, tom lane
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 17+ messages in thread
end of thread, other threads:[~2011-05-25 22:18 UTC | newest]
Thread overview: 17+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-05-09 16:25 Sorting Issue Ozer, Pam <pozer@automotive.com>
2011-05-09 19:37 ` Emi Lu <emilu@encs.concordia.ca>
2011-05-09 19:39 ` Ozer, Pam <pozer@automotive.com>
2011-05-09 19:52 ` Emi Lu <emilu@encs.concordia.ca>
2011-05-09 19:52 ` Ozer, Pam <pozer@automotive.com>
2011-05-09 20:38 ` Emi Lu <emilu@encs.concordia.ca>
2011-05-09 21:08 ` Samuel Gendler <sgendler@ideasculptor.com>
2011-05-09 21:28 ` Tom Lane <tgl@sss.pgh.pa.us>
2011-05-09 22:13 ` Ozer, Pam <pozer@automotive.com>
2011-05-10 15:07 ` Ozer, Pam <pozer@automotive.com>
2011-05-10 15:26 ` Ozer, Pam <pozer@automotive.com>
2011-05-10 16:47 ` Tom Lane <tgl@sss.pgh.pa.us>
2011-05-10 17:45 ` Samuel Gendler <sgendler@ideasculptor.com>
2011-05-10 17:56 ` Scott Marlowe <scott.marlowe@gmail.com>
2011-05-18 22:22 ` Ozer, Pam <pozer@automotive.com>
2011-05-25 22:18 ` Ozer, Pam <pozer@automotive.com>
2011-05-10 16:57 ` Edward W. Rouse <erouse@comsquared.com>
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