pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
"correct" sorting.
4+ messages / 4 participants
[nested] [flat]

* "correct" sorting.
@ 2001-05-03 04:40 Jeff MacDonald <jeff@tht.net>
  2001-05-03 18:16 ` RE: "correct" sorting. Gerald Gutierrez <gutz@kalador.com>
  2001-05-04 15:50 ` Re: "correct" sorting. Jeff Self <jself@greatbridge.com>
  0 siblings, 2 replies; 4+ messages in thread

From: Jeff MacDonald @ 2001-05-03 04:40 UTC (permalink / raw)
  To: pgsql-sql

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)




^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* RE: "correct" sorting.
  2001-05-03 04:40 "correct" sorting. Jeff MacDonald <jeff@tht.net>
@ 2001-05-03 18:16 ` Gerald Gutierrez <gutz@kalador.com>
  2001-05-03 20:44   ` RE: "correct" sorting. Joel Burton <jburton@scw.org>
  1 sibling, 1 reply; 4+ messages in thread

From: Gerald Gutierrez @ 2001-05-03 18:16 UTC (permalink / raw)
  To: Jeff MacDonald <jeff@tht.net>; pgsql-sql



That list looks like an ordering that is custom to your application. The
latter sort created by the SQL seems more typical (correct?) to me.

I would return the table unsorted, and write the sorting routine in the
calling code instead.


-----Original Message-----
From: pgsql-sql-owner@postgresql.org
[mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Jeff MacDonald
Sent: Wednesday, May 02, 2001 9:41 PM
To: pgsql-sql@postgresql.org
Subject: [SQL] "correct" sorting.


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)


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)




^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* RE: "correct" sorting.
  2001-05-03 04:40 "correct" sorting. Jeff MacDonald <jeff@tht.net>
  2001-05-03 18:16 ` RE: "correct" sorting. Gerald Gutierrez <gutz@kalador.com>
@ 2001-05-03 20:44   ` Joel Burton <jburton@scw.org>
  0 siblings, 0 replies; 4+ messages in thread

From: Joel Burton @ 2001-05-03 20:44 UTC (permalink / raw)
  To: Gerald Gutierrez <gutz@kalador.com>; +Cc: Jeff MacDonald <jeff@tht.net>; pgsql-sql

On Thu, 3 May 2001, Gerald Gutierrez 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)

Hmmm... howzabout

<ugly hack>

create a function order_val(text) returning an integer, which is
equal to the the input, coerced into an integer (for simple things, like
10, 20, etc.), but equal to 10.01 for 10a, 10.02 for 10b, 10.25 for 10z.
(pl/perl, pl/tcl, or pl/python might be a quicker choice for this than
pl/pgsql)

You could then

SELECT id FROM tbl ORDER BY order_val(id);

And you could even index on order_val(id), so that it runs a bit faster.

</ugly hack>

-- 
Joel Burton   <jburton@scw.org>
Director of Information Systems, Support Center of Washington




^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: "correct" sorting.
  2001-05-03 04:40 "correct" sorting. Jeff MacDonald <jeff@tht.net>
@ 2001-05-04 15:50 ` Jeff Self <jself@greatbridge.com>
  1 sibling, 0 replies; 4+ messages in thread

From: Jeff Self @ 2001-05-04 15:50 UTC (permalink / raw)
  To: Jeff MacDonald <jeff@tht.net>; +Cc: pgsql-sql

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




^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~2001-05-04 15:50 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-05-03 04:40 "correct" sorting. Jeff MacDonald <jeff@tht.net>
2001-05-03 18:16 ` Gerald Gutierrez <gutz@kalador.com>
2001-05-03 20:44   ` Joel Burton <jburton@scw.org>
2001-05-04 15:50 ` Jeff Self <jself@greatbridge.com>

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