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