Received: from jselfpc.us.greatbridge.com (jselfpc.us.greatbridge.com [65.196.69.74]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f44FpQA38276 for ; Fri, 4 May 2001 11:51:26 -0400 (EDT) (envelope-from jself@greatbridge.com) Received: from localhost (jself@localhost) by jselfpc.us.greatbridge.com (8.11.2/8.11.2) with ESMTP id f44FoVi08466; Fri, 4 May 2001 11:50:35 -0400 X-Authentication-Warning: jselfpc.us.greatbridge.com: jself owned process doing -bs Date: Fri, 4 May 2001 11:50:31 -0400 (EDT) From: Jeff Self X-X-Sender: To: Jeff MacDonald cc: Subject: Re: "correct" sorting. In-Reply-To: Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Archive-Number: 200105/22 X-Sequence-Number: 2820 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