SQL, ODER BY

[hansu]

Hi,

I use an SQL statement to fill a recordset.

"SELECT position FROM orderpositions ORDER BY position ASC"

That orders the recordset as follows:
1
10
11
12
2
3
4 ...
Is there a possibility to modify the SQL statement that the result is in a consecutive order?
10 follows 9, 20 follows 19 and so on?
(For some reason I don't want to use the Recordset.Sort method)

Thanks for your assistance.
hansu

 

[DangerPowers]

Try converting the position column to a number in the order by clause:

SELECT position FROM orderpositions ORDER BY cast(position as int) ASC

 

[hansu]

Thanks DangerPowers

But the problem is that the position can contain numeric as well as alphanumeric values.
Any solution?

 

[SemperFiDownUnda]

Your stuffed. You either to a numeric sort or alphabetic.

There is no sort (unless you want to code it yourself)
that conciders

AA as greater than B. Sorts do NOT take lenght of the string into account really

 

[SemperFiDownUnda]

I take that back. Left pad your data with spaces then sort it.