Обсуждение: "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 
------1101a1b2202a2b3303a3b
(12 rows)

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
------1101a1b2202a2b3303a3b
(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)

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

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