How about something like:
... ORDER BY CASE WHEN varchar_field < 'A'
THEN lpad(varchar_field, x, '0')
ELSE varchar_field END;
Where "x" is a number equal to (or greater than) the
length of the longest integer string in the column.
Padding your integer values with zeros, all to equal
length, will cause the ascii sort to be equivalent to
a numeric sort, for the integer values.
--- Ben Ausden <benedict@navyblue.com> wrote:
> thanks Mitch.
> the problem is that casting to int won't work for
> non-numeric data
> (obviously), and I can't guarantee that values in
> this column will
> definitely be numeric... I should have made that
> clearer in my post. Ideally
> I'd like to sort numbers in numeric order and text
> in regular alphabetic
> order, like: 1,2,3,40,50,a,b,c,d,e...[etc]
>
>
> -ben
>
> > -----Original Message-----
> > From: Mitch Vincent [mailto:mitch@doot.org]
> > Sent: 28 January 2002 21:07
> > To: Ben Ausden; pgsql-general@postgresql.org
> > Subject: Re: [GENERAL] numeric SORT order
> >
> >
> > select * from whatever order by
> field1::text::integer;
> >
> > seems to work..
> >
> >
> > ----- Original Message -----
> > From: "Ben Ausden" <benedict@navyblue.com>
> > To: <pgsql-general@postgresql.org>
> > Sent: Monday, January 28, 2002 1:47 PM
> > Subject: [GENERAL] numeric SORT order
> >
> >
> > > Hi,
> > >
> > >
> > > Is there any way to persuade postgresql to sort
> a VARCHAR column in
> > numeric
> > > order first, and then text order?
> > >
> > > e.g
> > >
> > > 1, 2, 3, 10, 20, 25, 30, 40
> > >
> > > instead of:
> > >
> > > 1, 10, 2, 20, 25, 3, 30, 40
> >
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please
> send an appropriate
> subscribe-nomail command to majordomo@postgresql.org
> so that your
> message can get through to the mailing list cleanly
__________________________________________________
Do You Yahoo!?
Great stuff seeking new owners in Yahoo! Auctions!
http://auctions.yahoo.com
