Re: 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