Thread: Sorting Issue

Sorting Issue

From

"Ozer, Pam"

Date:

09 May 2011, 16:27:00

<div class="WordSection1"><p class="MsoNormal">I have the following query<p class="MsoNormal">Select
 VehicleTrimAbbreviated,VehicleTrimAbbreviatedId<p class="MsoNormal">from VehicleTrimAbbreviated<p
class="MsoNormal">WherevehicleTrimAbbreviated like 'CX%'<p class="MsoNormal">order by VehicleTrimAbbreviated asc<p
class="MsoNormal"> <pclass="MsoNormal"> <p class="MsoNormal">Results:<p class="MsoNormal">532;"CX Hatchback"<p
class="MsoNormal">536;"CXLMinivan"<p class="MsoNormal">3255;"CXL Premium Sedan"<p class="MsoNormal">537;"CXL Sedan"<p
class="MsoNormal">538;"CXLSport Utility"<p class="MsoNormal">3319;"CXL Turbo Sedan"<p class="MsoNormal">533;"CX
Minivan"<pclass="MsoNormal">1959;"CX Plus Minivan"<p class="MsoNormal">534;"CX Sedan"<p class="MsoNormal">535;"CX Sport
Utility"<pclass="MsoNormal">539;"CXS Sedan"<p class="MsoNormal"> <p class="MsoNormal">Why would this not sort
correctly? All the CX should be first, then CXL, Then CXS<p class="MsoNormal"> <p class="MsoNormal"> <p
class="MsoNormal"> <pclass="MsoNormal">Here is the table definition:<p class="MsoNormal"> <p class="MsoNormal">CREATE
TABLEvehicletrimabbreviated<p class="MsoNormal">(<p class="MsoNormal">  vehicletrimabbreviatedid integer NOT NULL,<p
class="MsoNormal"> vehicletrimabbreviated character varying(64),<p class="MsoNormal">  vehiclesubmodelid integer,<p
class="MsoNormal"> vehiclebodystyleid smallint,<p class="MsoNormal">  vehiclebodystylegroupid smallint<p
class="MsoNormal">)<pclass="MsoNormal">WITH (<p class="MsoNormal">  OIDS=FALSE<p class="MsoNormal">);<p
class="MsoNormal">ALTERTABLE vehicletrimabbreviated OWNER TO app_user;<p class="MsoNormal"> <p class="MsoNormal">--
Index:vehicletrimabbreviated_i00<p class="MsoNormal"> <p class="MsoNormal">-- DROP INDEX vehicletrimabbreviated_i00;<p
class="MsoNormal"> <pclass="MsoNormal">CREATE UNIQUE INDEX vehicletrimabbreviated_i00<p class="MsoNormal">  ON
vehicletrimabbreviated<pclass="MsoNormal">  USING btree<p class="MsoNormal">  (vehicletrimabbreviatedid);<p
class="MsoNormal"> <pclass="MsoNormal">-- Index: vehicletrimabbreviated_i01<p class="MsoNormal"> <p
class="MsoNormal">--DROP INDEX vehicletrimabbreviated_i01;<p class="MsoNormal"> <p class="MsoNormal">CREATE INDEX
vehicletrimabbreviated_i01<pclass="MsoNormal">  ON vehicletrimabbreviated<p class="MsoNormal">  USING btree<p
class="MsoNormal"> (lower(vehicletrimabbreviated::text));<p class="MsoNormal"> <p class="MsoNormal">-- Index:
vehicletrimabbreviated_i02<pclass="MsoNormal"> <p class="MsoNormal">-- DROP INDEX vehicletrimabbreviated_i02;<p
class="MsoNormal"> <pclass="MsoNormal">CREATE INDEX vehicletrimabbreviated_i02<p class="MsoNormal">  ON
vehicletrimabbreviated<pclass="MsoNormal">  USING btree<p class="MsoNormal">  (vehiclesubmodelid,
vehiclebodystyleid);<pclass="MsoNormal"> <p class="MsoNormal">-- Index: vehicletrimabbreviated_i03<p
class="MsoNormal"> <pclass="MsoNormal">-- DROP INDEX vehicletrimabbreviated_i03;<p class="MsoNormal"> <p
class="MsoNormal">CREATEINDEX vehicletrimabbreviated_i03<p class="MsoNormal">  ON vehicletrimabbreviated<p
class="MsoNormal"> USING btree<p class="MsoNormal">  (vehiclebodystyleid);<p class="MsoNormal"> <p class="MsoNormal">--
Index:vehicletrimabbreviated_i04<p class="MsoNormal"> <p class="MsoNormal">-- DROP INDEX vehicletrimabbreviated_i04;<p
class="MsoNormal"> <pclass="MsoNormal">CREATE INDEX vehicletrimabbreviated_i04<p class="MsoNormal">  ON
vehicletrimabbreviated<pclass="MsoNormal">  USING btree<p class="MsoNormal">  (vehiclebodystylegroupid,
vehiclesubmodelid,vehiclebodystyleid);<p class="MsoNormal"> <p class="MsoNormal">-- Index: vehicletrimabbreviated_i05<p
class="MsoNormal"> <pclass="MsoNormal">-- DROP INDEX vehicletrimabbreviated_i05;<p class="MsoNormal"> <p
class="MsoNormal">CREATEINDEX vehicletrimabbreviated_i05<p class="MsoNormal">  ON vehicletrimabbreviated<p
class="MsoNormal"> USING btree<p class="MsoNormal">  (lower(vehicletrimabbreviated::text), vehiclesubmodelid);<p
class="MsoNormal"> <pclass="MsoNormal">-- Index: vehicletrimabbreviated_i06<p class="MsoNormal"> <p
class="MsoNormal">--DROP INDEX vehicletrimabbreviated_i06;<p class="MsoNormal"> <p class="MsoNormal">CREATE INDEX
vehicletrimabbreviated_i06<pclass="MsoNormal">  ON vehicletrimabbreviated<p class="MsoNormal">  USING btree<p
class="MsoNormal"> (vehiclesubmodelid, vehiclebodystyleid);<p class="MsoNormal"> <div id="content"><p
class="MsoNormal"><b><spanstyle="font-size:11.5pt;font-family:"Arial","sans-serif";color:black">Pam Ozer</span></b><p
class="MsoNormal"><i><spanstyle="font-size:10.5pt;font-family:"Arial","sans-serif";color:black">Data
Architect</span></i><pclass="MsoNormal"><span style="font-size:9.0pt;font-family:"Arial","sans-serif";color:black"><a
href="mailto:pozer@automotive.com"><spanstyle="color:blue">pozer@automotive.com</span></a> </span><table border="0"
cellpadding="0"class="MsoNormalTable"><tr><td style="padding:0in 0in 0in 0in"><p class="MsoNormal"><b><span
style="font-size:9.0pt;font-family:"Arial","sans-serif";color:black">tel.</span></b><span
style="font-size:9.0pt;font-family:"Arial","sans-serif";color:black">949.705.3468</span></td></tr></table></div><table
border="0"cellpadding="0" cellspacing="0" class="MsoNormalTable" style="width:100.0%" width="100%"><tr
style="height:39.0pt"><tdstyle="width:79.5pt;padding:0in 0in 0in 0in;height:39.0pt" width="106"><p align="center"
class="MsoNormal"style="text-align:center"><span
style="font-size:9.0pt;font-family:"Arial","sans-serif";color:black"><imgalt="Source Interlink Media logo" border="0"
height="52"id="_x0000_i1026" src="cid:image001.jpg@01CC0E2B.02393D30" width="106" /></span><span
style="font-size:9.0pt;font-family:"Arial","sans-serif";color:black"></span></td><tdstyle="width:3.75pt;padding:7.5pt
7.5pt7.5pt 7.5pt;height:39.0pt" width="5"><p align="center" class="MsoNormal" style="text-align:center"><span
style="font-size:9.0pt;font-family:"Arial","sans-serif";color:black"><imgalt="vertical line" border="0" height="50"
id="_x0000_i1025"src="cid:image002.jpg@01CC0E2B.02393D30" width="1" /></span><span
style="font-size:9.0pt;font-family:"Arial","sans-serif";color:black"></span></td><tdstyle="padding:0in 0in 0in
0in;height:39.0pt"><pclass="MsoNormal"><b><i><span
style="font-size:10.5pt;font-family:"Arial","sans-serif";color:black">SourceInterlink Media</span></i></b><p
class="MsoNormal"><spanstyle="font-size:9.0pt;font-family:"Arial","sans-serif";color:black">1733 Alton Pkwy Suite 100,
Irvine,CA 92606</span><p class="MsoNormal"><span
style="font-size:9.0pt;font-family:"Arial","sans-serif";color:black"><ahref="http://www.simautomotive.com"><span
style="color:blue">www.simautomotive.com</span></a></span></td></tr></table><div id="confid_notice"
style="border:none;border-top:solid#D4D4D4 1.0pt;padding:8.0pt 0in 0in 0in"><p class="MsoNormal"><i><span
style="font-size:9.0pt;font-family:"Arial","sans-serif";color:#CDCDCD">ConfidentialityNotice- 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
intendedrecipient, you are hereby notified that any disclosure of; dissemination of; distribution of; copying of; or,
takingany action in reliance upon this communication, is strictly prohibited. If you have received this communication
inerror, please immediately notify us by telephone, (949)-705-3000, and destroy all copies of this communication. Thank
you.</span></i></div><pclass="MsoNormal"><span style="font-size:12.0pt;font-family:"Times New Roman","serif""><br /><br
/></span></div>

Re: Sorting Issue

From

Emi Lu

Date:

09 May 2011, 20:08:37

> 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;

Re: Sorting Issue

From

"Ozer, Pam"

Date:

09 May 2011, 20:09:19

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;

Re: Sorting Issue

From

Emi Lu

Date:

09 May 2011, 20:17:24

> 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;
>
>

Re: Sorting Issue

From

"Ozer, Pam"

Date:

09 May 2011, 20:17:38

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;
>
>

Re: Sorting Issue

From

Emi Lu

Date:

09 May 2011, 20:39:14

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

Re: Sorting Issue

From

Samuel Gendler

Date:

09 May 2011, 21:08:53

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"

Re: Sorting Issue

From

Tom Lane

Date:

09 May 2011, 21:29:41

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

Re: Sorting Issue

From

"Ozer, Pam"

Date:

09 May 2011, 22:13:26

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

Re: Sorting Issue

From

"Ozer, Pam"

Date:

10 May 2011, 15:07:52

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

Re: Sorting Issue

From

"Ozer, Pam"

Date:

10 May 2011, 15:26:47

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

Re: Sorting Issue

From

Tom Lane

Date:

10 May 2011, 16:47:50

"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

Re: Sorting Issue

From

"Edward W. Rouse"

Date:

10 May 2011, 17:11:15

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

Re: Sorting Issue

From

Samuel Gendler

Date:

10 May 2011, 17:53:36

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

Re: Sorting Issue

From

Scott Marlowe

Date:

10 May 2011, 17:58:19

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

Re: Sorting Issue

From

"Ozer, Pam"

Date:

18 May 2011, 22:22:10

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

Re: Sorting Issue

From

"Ozer, Pam"

Date:

25 May 2011, 22:35:42

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