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



---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

               http://archives.postgresql.org/

Reply via email to