From: Jeff Self <jself@greatbridge.com>
To: Jeff MacDonald <jeff@tht.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: "correct" sorting.
Date: Fri, 4 May 2001 11:50:31 -0400 (EDT)
Message-ID: <Pine.LNX.4.33.0105041145110.8460-100000@jselfpc.us.greatbridge.com> (raw)
In-Reply-To: <Pine.NEB.4.05.10105030038150.18863-100000@tht.net>
You're trying to compare apples and oranges. Since the field is of type
text, it will sort by text. Therefore, the result will be 1 then 10 then
1a and so forth. It is sorting based on ASCII. The only way to get it to
sort in proper numerical order is to make the field a numeric field. But
of course you won't be able to use characters in that. Therefore, create a
second field called revision or whatever of text. Now you can sort
correctly with:
select * from foo order by var1,revision;
On Thu, 3 May 2001, Jeff
MacDonald wrote:
> Hi folks,
>
> say i have a text field with teh values
>
> 1,2,3,10,20,30,1a,1b,2a,2b
>
> and i want to sort it so i get,
>
> 1
> 1a
> 1b
> 2
> 2a
> 2b
> 3
> 10
> 20
> 30
>
> is there anyway to do that with postgresql ?
> below is what actually happens.
>
> jeff=> select * from foo order by var1;
> var1
> ------
> 1
> 10
> 1a
> 1b
> 2
> 20
> 2a
> 2b
> 3
> 30
> 3a
> 3b
> (12 rows)
>
--
Jeff Self
Information Specialist
Great Bridge, LLC
www.greatbridge.com | www.greatbridge.org
Norfolk, VA
(757)233-5570
jeff.self@greatbridge.com
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: jself@greatbridge.com, jeff@tht.net
Subject: Re: "correct" sorting.
In-Reply-To: <Pine.LNX.4.33.0105041145110.8460-100000@jselfpc.us.greatbridge.com>
References: <Pine.NEB.4.05.10105030038150.18863-100000@tht.net> <Pine.LNX.4.33.0105041145110.8460-100000@jselfpc.us.greatbridge.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox