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