Received: from maia.hub.org (maia-5.hub.org [200.46.204.29])
	by mail.postgresql.org (Postfix) with ESMTP id 72B11133616B
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Mon,  9 May 2011 18:08:53 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024)
	with ESMTP id 35600-08 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Mon,  9 May 2011 21:08:42 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from mail-pv0-f174.google.com (mail-pv0-f174.google.com
	[74.125.83.174])
	by mail.postgresql.org (Postfix) with ESMTP id DE43D1337B97
	for <pgsql-sql@postgresql.org>; Mon,  9 May 2011 18:08:41 -0300 (ADT)
Received: by pvg12 with SMTP id 12so2529598pvg.19
	for <pgsql-sql@postgresql.org>; Mon, 09 May 2011 14:08:41 -0700 (PDT)
MIME-Version: 1.0
Received: by 10.68.50.73 with SMTP id a9mr10329874pbo.237.1304975321047; Mon,
	09 May 2011 14:08:41 -0700 (PDT)
Received: by 10.68.48.68 with HTTP; Mon, 9 May 2011 14:08:41 -0700 (PDT)
X-Originating-IP: [66.215.95.51]
In-Reply-To: <4DC850CC.50409@encs.concordia.ca>
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>
	<4DC850CC.50409@encs.concordia.ca>
Date: Mon, 9 May 2011 14:08:41 -0700
Message-ID: <BANLkTikJhPpiM-rV9DX1rp_1dJhbTGOtZQ@mail.gmail.com>
Subject: Re: Sorting Issue
From: Samuel Gendler <sgendler@ideasculptor.com>
To: emilu@encs.concordia.ca
Cc: "Ozer, Pam" <pozer@automotive.com>, pgsql-sql@postgresql.org
Content-Type: multipart/alternative; boundary=bcaec54306fe73a2e304a2de3efd
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.899 tagged_above=-5 required=5 tests=BAYES_00=-1.9,
	HTML_MESSAGE=0.001
X-Spam-Level: 
X-Archive-Number: 201105/56
X-Sequence-Number: 35340

--bcaec54306fe73a2e304a2de3efd
Content-Type: text/plain; charset=ISO-8859-1

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"

--bcaec54306fe73a2e304a2de3efd
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

<br><br><div class=3D"gmail_quote">On Mon, May 9, 2011 at 1:38 PM, Emi Lu <=
span dir=3D"ltr">&lt;<a href=3D"mailto:emilu@encs.concordia.ca">emilu@encs.=
concordia.ca</a>&gt;</span> wrote:<br><blockquote class=3D"gmail_quote" sty=
le=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex;">
Hi Pam,<div class=3D"im"><br>
<br>
&gt;&gt; Select VehicleTrimAbbreviated, VehicleTrimAbbreviatedId<br>
&gt;&gt; from VehicleTrimAbbreviated<br>
&gt;&gt; Where vehicleTrimAbbreviated like &#39;CX%&#39;<br>
&gt;&gt; order by<br>
&gt;&gt;<br>
&gt;&gt; split_part(VehicleTrimAbbreviated, &#39; &#39;, 1) asc,<br>
&gt;&gt; split_part(VehicleTrimAbbreviated, &#39; &#39;, 2) asc;<br>
<br></div>
This query works, right?<br>
<br>
Reason:<br>
=3D=3D=3D=3D=3D=3D<br>
. split_part(VehicleTrimAbbreviated, &#39; &#39;, 1) return the string befo=
re the blank<br>
<br>
. split_part(VehicleTrimAbbreviated, &#39; &#39;, 1) return the string afte=
r the blank<br>
<br>
So<br>
[1] you order by CX, CXL, CXS first<br>
[2] you order by second part &quot;Hatchback, Minivan... &quot;<br>
<br>
Is there clear now?<br>
<br></blockquote><div><br></div><div>Not really. =A0It should sort alphabet=
ically the same in either case. =A0Here&#39;s an alphabetic sort of similar=
 strings in python</div><div><br></div><div>&gt;&gt;&gt; a =3D [&quot;CX Ha=
tchback&quot;, &quot;CXL Minivan&quot;, &quot;CXL Premium Sedan&quot;, &quo=
t;CXL Sedan&quot;, &quot;CXL Sport Utility&quot;, &quot;CXL Turbo Sedan&quo=
t;, &quot;CX Minivan&quot;, &quot;CXS Sedan&quot;]</div>
<div>&gt;&gt;&gt; a.sort()</div><div>&gt;&gt;&gt; a</div><div><div>[&#39;CX=
 Hatchback&#39;, &#39;CX Minivan&#39;, &#39;CXL Minivan&#39;, &#39;CXL Prem=
ium Sedan&#39;, &#39;CXL Sedan&#39;, &#39;CXL Sport Utility&#39;, &#39;CXL =
Turbo Sedan&#39;, &#39;CXS Sedan&#39;]</div>
</div><div><br></div><div>It&#39;s not at all clear why they are not coming=
 out of the db in alphabetically sorted order when =A0the query includes &q=
uot;order by VehicleTrimAbbreviated asc&quot;</div><div><br></div><div>=A0<=
/div>
</div>

--bcaec54306fe73a2e304a2de3efd--