Re: Sorting

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