Sorting

am Mon, dem 08.01.2007, um 10:21:38 -0600 mailte Bart McFarling folgendes:

I have a column that is a varchar(6) I need to sort it by the rows that are
integers 1st then the character ones or vice versa, I just need the values that
can be converted to integer to sort by their numeric value.

i.e
1, 2, 3, 4, 5, 10, 11, A, B, C
instead of
1, 10, 11, 2, 3, 4, 5, A, B, C

Any suggestions?

perhaps something like this:

test=*# select * from foo;
w
----
10
1
A
3
C
(5 rows)

Time: 1.349 ms
test=*# select w, case when w ~ '^[0-9]*$' then w::int else 10000 end from foo order by 2,1;
w | case
----+-------
1 | 1
3 | 3
10 | 10
A | 10000
C | 10000
(5 rows)

Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net

On m�n, 2007-01-08 at 17:59 +0100, A. Kretschmer wrote:

am Mon, dem 08.01.2007, um 10:21:38 -0600 mailte Bart McFarling folgendes:

I have a column that is a varchar(6) I need to sort it by the rows that are
integers 1st then the character ones or vice versa, I just need the values that
can be converted to integer to sort by their numeric value.

i.e
1, 2, 3, 4, 5, 10, 11, A, B, C
instead of
1, 10, 11, 2, 3, 4, 5, A, B, C

Any suggestions?

perhaps something like this:

test=*# select * from foo;
w
----
10
1
A
3
C
(5 rows)

Time: 1.349 ms
test=*# select w, case when w ~ '^[0-9]*$' then w::int else 10000 end from foo order by 2,1;

possible improvements:
a) w ~ '^[0-9]+$'
b) use NULL instead of 10000

gnari

Ragnar <gnari@hive.is> schrieb:

test=*# select w, case when w ~ '^[0-9]*$' then w::int else 10000 end from foo order by 2,1;

possible improvements:
a) w ~ '^[0-9]+$'
b) use NULL instead of 10000

Thanks, right.

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." (unknow)
Kaufbach, Saxony, Germany, Europe. N 51.05082�, E 13.56889�

Thanks,

-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Andreas Kretschmer
Sent: Monday, January 08, 2007 11:19 AM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Sorting

Ragnar <gnari@hive.is> schrieb:

test=*# select w, case when w ~ '^[0-9]*$' then w::int else 10000
end from foo order by 2,1;

possible improvements:
a) w ~ '^[0-9]+$'
b) use NULL instead of 10000

Thanks, right.

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." (unknow)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend