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
