Sorting alphanumeric data

[DJ]

Hello,

I have sorting problem with a column in the database (string) that
stores alphanumeric data. The data is A1, A2, A3..A10, A11, A12.

When I sort by column type, I get A1, A10, A11, A12 but I would like
to get A1, A2, A3...A10, A11, A12. Is there anything I can try with
orderby="columntype"? What other kind of operations does orderby
allow? (I saw field.upper and field.month).

Thanks,
Sebastian

[Jonathan Lundell]

What you're looking for is a natural (as opposed to lexical) sort, which most (all?) databases lack.

A common solution is to create another column to serve as a sort key. In your example, it might be A01, A02, ... A10. Or two columns, one with the leading alpha, sorted lexically, and the other with the number, sorted numerically (and a two-column major-minor sort).

[Thadeus Burgess]

It is possible to create SQL functions that will represent your
natural sort. You can use db.executesql("") to call the function.

Read the following comments in the below link for examples on
performing this with postgresql.

http://www.postgresql.org/docs/8.0/interactive/queries-order.html

--
Thadeus

[DJ]

Thanks Thadeus and Jonathan for your suggestions.

-S

On Jul 23, 10:06 pm, Thadeus Burgess <thade...@thadeusb.com> wrote:
> It is possible to create SQL functions that will represent your
> natural sort. You can use db.executesql("") to call the function.
>
> Read the following comments in the below link for examples on
> performing this with postgresql.
>
> http://www.postgresql.org/docs/8.0/interactive/queries-order.html
>
> --
> Thadeus
>