numeric SORT order

select * from whatever order by field1::text::integer;

seems to work..

----- Original Message -----
From: "Ben Ausden" <benedict@navyblue.com>
To: <pgsql-general@postgresql.org>
Sent: Monday, January 28, 2002 1:47 PM
Subject: [GENERAL] numeric SORT order

Hi,

Is there any way to persuade postgresql to sort a VARCHAR column in

numeric

test=# create table vc ( n varchar);
CREATE
test=# insert into vc values ('1');
INSERT 304426 1
(...etc...)
test=# select n from vc order by n;
n
----
1
10
2
20
25
3
30
40
(8 rows)

test=# select n from vc order by int8(n);
n
----
1
2
3
10
20
25
30
40
(8 rows)

Hope this helps.

Allan.

Ben Ausden wrote:

thanks Mitch.
the problem is that casting to int won't work for non-numeric data
(obviously), and I can't guarantee that values in this column will
definitely be numeric... I should have made that clearer in my post. Ideally
I'd like to sort numbers in numeric order and text in regular alphabetic
order, like: 1,2,3,40,50,a,b,c,d,e...[etc]

-ben

How about something like:
... ORDER BY CASE WHEN varchar_field < 'A'
THEN lpad(varchar_field, x, '0')
ELSE varchar_field END;
Where "x" is a number equal to (or greater than) the
length of the longest integer string in the column.

Padding your integer values with zeros, all to equal
length, will cause the ascii sort to be equivalent to
a numeric sort, for the integer values.

--- Ben Ausden <benedict@navyblue.com> wrote:

thanks Mitch.
the problem is that casting to int won't work for
non-numeric data
(obviously), and I can't guarantee that values in
this column will
definitely be numeric... I should have made that
clearer in my post. Ideally
I'd like to sort numbers in numeric order and text
in regular alphabetic
order, like: 1,2,3,40,50,a,b,c,d,e...[etc]

-ben

-----Original Message-----
From: Mitch Vincent [mailto:mitch@doot.org]
Sent: 28 January 2002 21:07
To: Ben Ausden; pgsql-general@postgresql.org
Subject: Re: [GENERAL] numeric SORT order

select * from whatever order by

field1::text::integer;

seems to work..

----- Original Message -----
From: "Ben Ausden" <benedict@navyblue.com>
To: <pgsql-general@postgresql.org>
Sent: Monday, January 28, 2002 1:47 PM
Subject: [GENERAL] numeric SORT order

Hi,

Is there any way to persuade postgresql to sort

a VARCHAR column in

numeric

order first, and then text order?

e.g

1, 2, 3, 10, 20, 25, 30, 40

instead of:

1, 10, 2, 20, 25, 3, 30, 40

---------------------------(end of
broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please
send an appropriate
subscribe-nomail command to majordomo@postgresql.org
so that your
message can get through to the mailing list cleanly

__________________________________________________
Do You Yahoo!?
Great stuff seeking new owners in Yahoo! Auctions!
http://auctions.yahoo.com

Ben Ausden <benedict@navyblue.com> writes:

thanks Mitch.
the problem is that casting to int won't work for non-numeric data
(obviously), and I can't guarantee that values in this column will
definitely be numeric... I should have made that clearer in my post. Ideally
I'd like to sort numbers in numeric order and text in regular alphabetic
order, like: 1,2,3,40,50,a,b,c,d,e...[etc]

Then you'll need to write a custom comparison operator and do
"ORDER BY ... USING my_op".

-Doug
--
Let us cross over the river, and rest under the shade of the trees.
--T. J. Jackson, 1863