Thread: 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 From looking at the docs it seems that sort order is determined by the locale settings. Is this right, and would setting my locale to en_US cause postgres to use a different sort order? thanks, ben -- ben ausden
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 > > > From looking at the docs it seems that sort order is determined by the > locale settings. Is this right, and would setting my locale to en_US cause > postgres to use a different sort order? > > > thanks, > ben > > -- > ben ausden > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org >
test=# create table vc ( n varchar);
CREATE
test=# insert into vc values ('1');
INSERT 304426 1
(...etc...)
test=# select n from vc order by n;
n
----
1
10
2
20
25
3
30
40
(8 rows)
test=# select n from vc order by int8(n);
n
----
1
2
3
10
20
25
30
40
(8 rows)
Hope this helps.
Allan.
Ben Ausden wrote:
> 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
>
>
> From looking at the docs it seems that sort order is determined by the
> locale settings. Is this right, and would setting my locale to en_US cause
> postgres to use a different sort order?
>
>
> thanks,
> ben
>
>
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 >
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
Ben Ausden <benedict@navyblue.com> writes: > 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] Then you'll need to write a custom comparison operator and do "ORDER BY ... USING my_op". -Doug -- Let us cross over the river, and rest under the shade of the trees. --T. J. Jackson, 1863