From coolofall@hotmail.com Fri Oct  14 15:42:03 2011
Received: from maia.hub.org (maia-3.hub.org [200.46.204.243])
	by mail.postgresql.org (Postfix) with ESMTP id 564E3B5DCE9
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Fri, 14 Oct 2011 12:42:03 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia,
	port 10024) with ESMTP id 55875-04 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Fri, 14 Oct 2011 15:41:56 +0000 (UTC)
X-Greylist: domain auto-whitelisted by SQLgrey-1.8.0-rc2
Received: from snt0-omc2-s35.snt0.hotmail.com (snt0-omc2-s35.snt0.hotmail.com
	[65.55.90.110])
	by mail.postgresql.org (Postfix) with ESMTP id 5309EB5DC72
	for <pgsql-sql@postgresql.org>; Fri, 14 Oct 2011 12:41:48 -0300 (ADT)
Received: from SNT102-W49 ([65.55.90.71]) by snt0-omc2-s35.snt0.hotmail.com
	with Microsoft SMTPSVC(6.0.3790.4675);
	Fri, 14 Oct 2011 08:41:46 -0700
Message-ID: <SNT102-W49402A41C6B6599F082D35A8E10@phx.gbl>
Content-Type: multipart/alternative;
	boundary="_c4521273-7fe3-4069-938d-5f78d0f9f590_"
X-Originating-IP: [64.47.206.114]
From: James Bond <coolofall@hotmail.com>
To: <pgsql-sql@postgresql.org>
Subject: postgres sql help
Date: Fri, 14 Oct 2011 10:41:46 -0500
Importance: Normal
MIME-Version: 1.0
X-OriginalArrivalTime: 14 Oct 2011 15:41:46.0733 (UTC)
	FILETIME=[C7FC71D0:01CC8A87]
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-2.402 tagged_above=-5 required=5 tests=BAYES_00=-1.9,
	FREEMAIL_FROM=0.001, HTML_MESSAGE=0.001,
	RCVD_IN_DNSWL_NONE=-0.0001, RP_MATCHES_RCVD=-0.504
X-Spam-Level: 
X-Archive-Number: 201110/21
X-Sequence-Number: 35905

--_c4521273-7fe3-4069-938d-5f78d0f9f590_
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable


hi=2C i am fairly new in postgresql=2C so if anyone can help me would be gr=
eat
=20

if i simply do:
=20

select ver_no

from version

order by ver_no
=20

the result will be something like this:
=20

.1.3.1

.1.3.2.5.

.1.4.1.7.12

.1.4.11.14.7.

.1.4.3.109.1.

.1.4.8.66.
=20

so as you can see first 3 lines are ok=2C but how to make 1.4.3.109 come=20
before 1.4.11 because the third level "3" is smaller than "11".  i=20
understand they are stored as char so i used split_part function to=20
separate each numbers between "." in a separate column. but when i try=20
to convert those column into integer=2C i am getting an error msg saying:=20
ERROR:  invalid input syntax for type numeric: " "
=20

here is my code:
=20

select ver_no=2C duedate=2C status=2C

to_number(split_part(ver_no=2C '.'=2C 2)=2C '9999') a=2C

to_number(split_part(ver_no=2C '.'=2C 3)=2C '9999') b=2C

to_number(split_part(ver_no=2C '.'=2C 4)=2C '9999') c=2C

to_number(split_part(ver_no=2C '.'=2C 5)=2C '9999') d
=20
=20

from version
=20

order by a=2Cb=2Cc=2Cd
=20

I am not sure if i am heading towards wrong direction but can someone pleas=
e suggest or give me some other ideas to sort this. =20
=20

PS: i found some good solution in SQL Server but those commands are not use=
d in postgreSQL.

Thanks in advance

- James

 		 	   		  =

--_c4521273-7fe3-4069-938d-5f78d0f9f590_
Content-Type: text/html; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<html>
<head>
<style><!--
.hmmessage P
{
margin:0px=3B
padding:0px
}
body.hmmessage
{
font-size: 10pt=3B
font-family:Tahoma
}
--></style>
</head>
<body class=3D'hmmessage'><div dir=3D'ltr'>
hi=2C i am fairly new in postgresql=2C so if anyone can help me would be gr=
eat<br> <br>
if i simply do:<br> <br>
select ver_no<br>
from version<br>
order by ver_no<br> <br>
the result will be something like this:<br> <br>
.1.3.1<br>
.1.3.2.5.<br>
.1.4.1.7.12<br>
.1.4.11.14.7.<br>
.1.4.3.109.1.<br>
.1.4.8.66.<br> <br>
so as you can see first 3 lines are ok=2C but how to make 1.4.3.109 come=20
before 1.4.11 because the third level "3" is smaller than "11".  i=20
understand they are stored as char so i used split_part function to=20
separate each numbers between "." in a separate column. but when i try=20
to convert those column into integer=2C i am getting an error msg saying:=20
ERROR:  invalid input syntax for type numeric: " "<br> <br>
here is my code:<br> <br>
select ver_no=2C duedate=2C status=2C<br>
to_number(split_part(ver_no=2C '.'=2C 2)=2C '9999') a=2C<br>
to_number(split_part(ver_no=2C '.'=2C 3)=2C '9999') b=2C<br>
to_number(split_part(ver_no=2C '.'=2C 4)=2C '9999') c=2C<br>
to_number(split_part(ver_no=2C '.'=2C 5)=2C '9999') d<br> <br> <br>
from version<br> <br>
order by a=2Cb=2Cc=2Cd<br> <br>
I am not sure if i am heading towards wrong direction but can someone pleas=
e suggest or give me some other ideas to sort this.  <br> <br>
PS: i found some good solution in SQL Server but those commands are not use=
d in postgreSQL.<br><br>Thanks in advance<br><br>- James<br><br> 		 	   		 =
 </div></body>
</html>=

--_c4521273-7fe3-4069-938d-5f78d0f9f590_--



From akretschmer@spamfence.net Sun Oct  16 08:12:08 2011
Received: from maia.hub.org (maia-3.hub.org [200.46.204.243])
	by mail.postgresql.org (Postfix) with ESMTP id B65E7B5DBCE
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Sun, 16 Oct 2011 05:12:08 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia,
	port 10024) with ESMTP id 87975-01 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Sun, 16 Oct 2011 08:12:02 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.8.0-rc2
Received: from mailout02.ims-firmen.de (mailout02.ims-firmen.de
	[213.174.32.97])
	by mail.postgresql.org (Postfix) with ESMTP id C8A7BB5D803
	for <pgsql-sql@postgresql.org>; Sun, 16 Oct 2011 05:12:01 -0300 (ADT)
Received: from mailin04.ims-firmen.de ([192.168.1.113])
	by mailout02.ims-firmen.de with esmtp
	(envelope-from <andreas@a-kretschmer.de>) id 1RFLp9-00007y-HI
	for pgsql-sql@postgresql.org; Sun, 16 Oct 2011 10:11:59 +0200
Received: from [87.170.193.79] (helo=a-kretschmer.de)
	by mailin04.ims-firmen.de with esmtpsa (TLSv1:AES256-SHA:256)
	(envelope-from <andreas@a-kretschmer.de>) id 1RFLp9-0003CL-BI
	for pgsql-sql@postgresql.org; Sun, 16 Oct 2011 10:11:59 +0200
Received: from kretschmer by a-kretschmer.de with local (Exim 4.69)
	(envelope-from <andreas@a-kretschmer.de>) id 1RFLp8-0002Pk-4B
	for pgsql-sql@postgresql.org; Sun, 16 Oct 2011 10:11:58 +0200
Date: Sun, 16 Oct 2011 10:11:57 +0200
From: Andreas Kretschmer <akretschmer@spamfence.net>
To: pgsql-sql@postgresql.org
Subject: Re: postgres sql help
Message-ID: <20111016081157.GA8614@tux>
References: <SNT102-W49402A41C6B6599F082D35A8E10@phx.gbl>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <SNT102-W49402A41C6B6599F082D35A8E10@phx.gbl>
X-OS: Debian/GNU Linux - weil ich es mir Wert bin!
X-GPG-Fingerprint: EE16 3C01 7B9C 10F7 2C8B  3B86 4DB3 D9EE 7F45 84DA
X-Message-Flag: "Windows" is not the answer. "Windows" is the question and
	the answer is "no"!
X-Lugdd: Gerd Kube
X-Info: My name is root. Just root. And I am licensed to kill -9
User-Agent: Mutt/1.5.18 (2008-05-17)
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.9 tagged_above=-5 required=5 tests=BAYES_00=-1.9,
	RCVD_IN_DNSWL_NONE=-0.0001
X-Spam-Level: 
X-Archive-Number: 201110/23
X-Sequence-Number: 35907

James Bond <coolofall@hotmail.com> wrote:

> hi, i am fairly new in postgresql, so if anyone can help me would be great
> 
> if i simply do:
> 
> select ver_no
> from version
> order by ver_no
> 
> the result will be something like this:
> 
> .1.3.1
> .1.3.2.5.
> .1.4.1.7.12
> .1.4.11.14.7.
> .1.4.3.109.1.
> .1.4.8.66.
> 
> so as you can see first 3 lines are ok, but how to make 1.4.3.109 come before
> 1.4.11 because the third level "3" is smaller than "11". i understand they are
> stored as char so i used split_part function to separate each numbers between
> "." in a separate column. but when i try to convert those column into integer,
> i am getting an error msg saying: ERROR: invalid input syntax for type numeric:
> " "
> 
> here is my code:

test=*# select * from o;
       t
---------------
 .1.3.1
 .1.3.2.5.
 .1.4.1.7.12
 .1.4.11.14.7.
 .1.4.3.109.1.
 .1.4.8.66.
(6 rows)

Time: 0,262 ms
test=*# select * from o order by split_part(t,'.',2)::int,
split_part(t,'.',3)::int, split_part(t,'.',4)::int;
       t
---------------
 .1.3.1
 .1.3.2.5.
 .1.4.1.7.12
 .1.4.3.109.1.
 .1.4.8.66.
 .1.4.11.14.7.
(6 rows)

Time: 0,403 ms


Andreas
-- 
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°



From hari.fuchs@gmail.com Sun Oct  16 08:24:35 2011
Received: from maia.hub.org (maia-3.hub.org [200.46.204.243])
	by mail.postgresql.org (Postfix) with ESMTP id BD8DDB5DBCE
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Sun, 16 Oct 2011 05:24:35 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia,
	port 10024) with ESMTP id 87098-03 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Sun, 16 Oct 2011 08:24:25 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.8.0-rc2
Received: from lo.gmane.org (lo.gmane.org [80.91.229.12])
	by mail.postgresql.org (Postfix) with ESMTP id 31B79B5D80B
	for <pgsql-sql@postgresql.org>; Sun, 16 Oct 2011 05:24:24 -0300 (ADT)
Received: from list by lo.gmane.org with local (Exim 4.69)
	(envelope-from <sql-pgsql-sql@m.gmane.org>) id 1RFM19-0006Uo-2a
	for pgsql-sql@postgresql.org; Sun, 16 Oct 2011 10:24:23 +0200
Received: from 212.126.222.122 ([212.126.222.122])
	by main.gmane.org with esmtp (Gmexim 0.1 (Debian))
	id 1AlnuQ-0007hv-00
	for <pgsql-sql@postgresql.org>; Sun, 16 Oct 2011 10:24:23 +0200
Received: from hari.fuchs by 212.126.222.122 with local (Gmexim 0.1 (Debian))
	id 1AlnuQ-0007hv-00
	for <pgsql-sql@postgresql.org>; Sun, 16 Oct 2011 10:24:23 +0200
X-Injected-Via-Gmane: http://gmane.org/
To: pgsql-sql@postgresql.org
From: Harald Fuchs <hari.fuchs@gmail.com>
Subject: Re: postgres sql help
Date: Sun, 16 Oct 2011 10:24:10 +0200
Lines: 29
Message-ID: <86obxhtkd1.fsf@protecting.net>
References: <SNT102-W49402A41C6B6599F082D35A8E10@phx.gbl>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
X-Complaints-To: usenet@dough.gmane.org
X-Gmane-NNTP-Posting-Host: 212.126.222.122
X-Archive: encrypt
User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/23.1 (gnu/linux)
Cancel-Lock: sha1:8qVJCXnQIiWD4HErRP1JjsJwDyI=
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.229 tagged_above=-5 required=5 tests=BAYES_00=-1.9,
	FREEMAIL_FROM=0.001, RCVD_NUMERIC_HELO=1.164,
	RP_MATCHES_RCVD=-0.504, T_TO_NO_BRKTS_FREEMAIL=0.01
X-Spam-Level: 
X-Archive-Number: 201110/24
X-Sequence-Number: 35908

In article <SNT102-W49402A41C6B6599F082D35A8E10@phx.gbl>,
James Bond <coolofall@hotmail.com> writes:

> hi, i am fairly new in postgresql, so if anyone can help me would be great
> if i simply do:

> select ver_no
> from version
> order by ver_no

> the result will be something like this:

> .1.3.1
> .1.3.2.5.
> .1.4.1.7.12
> .1.4.11.14.7.
> .1.4.3.109.1.
> .1.4.8.66.

> so as you can see first 3 lines are ok, but how to make 1.4.3.109 come
> before 1.4.11 because the third level "3" is smaller than "11".

The query

  SELECT ver_no
  FROM version
  ORDER BY string_to_array(ver_no, '.', '')::int[]

should do what you want.




From tim@tim-landscheidt.de Mon Oct  17 20:44:42 2011
Received: from maia.hub.org (maia-2.hub.org [200.46.204.251])
	by mail.postgresql.org (Postfix) with ESMTP id CA140B5DC5D
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Mon, 17 Oct 2011 17:44:42 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia,
	port 10024) with ESMTP id 85214-01 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Mon, 17 Oct 2011 20:44:36 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.8.0-rc2
Received: from lo.gmane.org (lo.gmane.org [80.91.229.12])
	by mail.postgresql.org (Postfix) with ESMTP id 95DE9B5DC5C
	for <pgsql-sql@postgresql.org>; Mon, 17 Oct 2011 17:44:36 -0300 (ADT)
Received: from list by lo.gmane.org with local (Exim 4.69)
	(envelope-from <sql-pgsql-sql@m.gmane.org>) id 1RFu30-00029t-MM
	for pgsql-sql@postgresql.org; Mon, 17 Oct 2011 22:44:34 +0200
Received: from g224125007.adsl.alicedsl.de ([92.224.125.7])
	by main.gmane.org with esmtp (Gmexim 0.1 (Debian))
	id 1AlnuQ-0007hv-00
	for <pgsql-sql@postgresql.org>; Mon, 17 Oct 2011 22:44:34 +0200
Received: from tim by g224125007.adsl.alicedsl.de with local (Gmexim 0.1
	(Debian)) id 1AlnuQ-0007hv-00
	for <pgsql-sql@postgresql.org>; Mon, 17 Oct 2011 22:44:34 +0200
X-Injected-Via-Gmane: http://gmane.org/
Mail-Followup-To: pgsql-sql@postgresql.org
To: pgsql-sql@postgresql.org
From: Tim Landscheidt <tim@tim-landscheidt.de>
Subject: Re: postgres sql help
Date: Mon, 17 Oct 2011 20:44:21 +0000
Organization: <URI:http://www.tim-landscheidt.de/>
Lines: 34
Message-ID: <m3lisjs5zu.fsf@passepartout.tim-landscheidt.de>
References: <SNT102-W49402A41C6B6599F082D35A8E10@phx.gbl>
	<86obxhtkd1.fsf@protecting.net>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
X-Complaints-To: usenet@dough.gmane.org
X-Gmane-NNTP-Posting-Host: g224125007.adsl.alicedsl.de
Mail-Copies-To: never
User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/23.1 (gnu/linux)
Cancel-Lock: sha1:mEeeZNWFlBm/3uHcwfqHxCUxHJs=
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-2.404 tagged_above=-5 required=5 tests=BAYES_00=-1.9,
	RP_MATCHES_RCVD=-0.504
X-Spam-Level: 
X-Archive-Number: 201110/27
X-Sequence-Number: 35911

Harald Fuchs <hari.fuchs@gmail.com> wrote:

>> hi, i am fairly new in postgresql, so if anyone can help me would be great
>> if i simply do:

>> select ver_no
>> from version
>> order by ver_no

>> the result will be something like this:

>> .1.3.1
>> .1.3.2.5.
>> .1.4.1.7.12
>> .1.4.11.14.7.
>> .1.4.3.109.1.
>> .1.4.8.66.

>> so as you can see first 3 lines are ok, but how to make 1.4.3.109 come
>> before 1.4.11 because the third level "3" is smaller than "11".

> The query

>   SELECT ver_no
>   FROM version
>   ORDER BY string_to_array(ver_no, '.', '')::int[]

> should do what you want.

Really neat! :-) For pre-9.1, you have to "ORDER BY
string_to_array(TRIM('.' FROM ver_no), '.')::int[];",
though.

Tim