Subject | Re: [firebird-support] Natural Sort Order |
---|---|

Author | Paul Vinkenoog |

Post date | 2010-11-29T22:39:59Z |

thedevguy wrote:

1

10

20

25

100

123aef

1010

aef123

But what you seem to want is:

- return all pure numerals first, in lexicographical (not numerical) order;

- then return the rest, again lexicographically.

No, we don't have that ;-)

You could probably construct a select statement that returns a 0 in the first column if the value is a pure numeral, and a 1 if it isn't. Then return the value itself in the second column, and order by 1, 2. But that's only based on the ordering of the few values you have shown - I don't know if you want to order alpha characters as ABab, AaBb, or totally case-insensitive, for instance.

Paul Vinkenoog

> Does Firebird contain a natural sort order algorithm of some kind? We have an alphanumeric field that we would like to sort in the following order:Most natural sort algorithms would order these values like this:

>

> 1

> 10

> 100

> 1010

> 20

> 25

> 123aef

> aef123

1

10

20

25

100

123aef

1010

aef123

But what you seem to want is:

- return all pure numerals first, in lexicographical (not numerical) order;

- then return the rest, again lexicographically.

No, we don't have that ;-)

You could probably construct a select statement that returns a 0 in the first column if the value is a pure numeral, and a 1 if it isn't. Then return the value itself in the second column, and order by 1, 2. But that's only based on the ordering of the few values you have shown - I don't know if you want to order alpha characters as ABab, AaBb, or totally case-insensitive, for instance.

Paul Vinkenoog