pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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




view thread (4+ messages)

Message-ID: <Pine.LNX.4.33.0105041145110.8460-100000@jselfpc.us.greatbridge.com>
Permalink:  ../Pine.LNX.4.33.0105041145110.8460-100000@jselfpc.us.greatbridge.com/
Also on:    postgresql.org/message-id/Pine.LNX.4.33.0105041145110.8460-100000@jselfpc.us.greatbridge.com

 · 

reply

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