RE: "correct" sorting.

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