sql SORT order not working on numbers?

[Alpay Eno]

Hello all... I'm using asp to get records from an access database, very
similar to the way datagrid would work. The title of each column in my table
is a link that alternates the sort order between ascending and descending...

my problem is that text WILL change its sort order just fine but nubers are
not always in order. ie: if sort order is ASC (ascending) I might see 2000,
234, 789 (should be ordered but its not). I'm guessing that ASP is handing
the string as a text string (?) and getting confused, is there a way to
force ASP into treating the string as numerals if this is the case? any
other ideas? Thanks so much.

here is one of my sql commands in case you want to see it. "sort" is a
variable containing the recordset to sort by depending on which link is
clicked. I hope I didn't confuse the whole issue because of a lack of
caffiene over here :)
strsql = "SELECT * FROM comments ORDER BY " & sort & " DESC"

Thanks of the help, much appreciated.
Eno

[Mark Schupp]

Re: sql SORT order not working on numbers?

What is the data type of the column containing the number?

--
Mark Schupp
--
Head of Development
Integrity eLearning
Online Learning Solutions Provider
mschupp@ielearn ing.com

http://www.ielearning.com

714.637.9480 x17


"Alpay Eno" <eno@spamsux.co m> wrote in message
news:MxfPa.5539 $hY1.1399166@ne ws4.srv.hcvlny. cv.net...[color=blue]
> Hello all... I'm using asp to get records from an access database, very
> similar to the way datagrid would work. The title of each column in my[/color]
table[color=blue]
> is a link that alternates the sort order between ascending and[/color]
descending...[color=blue]
>
> my problem is that text WILL change its sort order just fine but nubers[/color]
are[color=blue]
> not always in order. ie: if sort order is ASC (ascending) I might see[/color]
2000,[color=blue]
> 234, 789 (should be ordered but its not). I'm guessing that ASP is handing
> the string as a text string (?) and getting confused, is there a way to
> force ASP into treating the string as numerals if this is the case? any
> other ideas? Thanks so much.
>
> here is one of my sql commands in case you want to see it. "sort" is a
> variable containing the recordset to sort by depending on which link is
> clicked. I hope I didn't confuse the whole issue because of a lack of
> caffiene over here :)
> strsql = "SELECT * FROM comments ORDER BY " & sort & " DESC"
>
> Thanks of the help, much appreciated.
> Eno
>
>
>[/color]

[Aaron Bertrand - MVP]

Re: sql SORT order not working on numbers?

> my problem is that text WILL change its sort order just fine but nubers
are[color=blue]
> not always in order. ie: if sort order is ASC (ascending) I might see[/color]
2000,[color=blue]
> 234, 789 (should be ordered but its not).[/color]

Looks like this "number" column is not numeric at all, but rather
CHAR/VARCHAR.

Try
ORDER BY CONVERT(INT, thatColumn)

Or try making the column a numeric data type, if it holds numeric data.