ORDER BY - "numerical string" problem

[Giles]

An ASP page outputs data from the query
"Select ThisAndThat from comments WHERE pageURL='" & pageURL & "' ORDER BY
threadID, datesent"
(Access mdb)
threadID is a string (OK, I know!), which means that 103 displays before 99.
Is there a way to write the SQL query to order them numerically? This would
be much easier for me than changing the data type and hunting down every
page that INSERTS or UPDATES the db.
Thanks, Giles

 

[Ray Costanzo [MVP]]

You should correct the data instead of applying a band-aid. But, if you
choose not to:

ORDER BY CInt(threadID)...

BUT NEXT TIME, STORE NUMERIC VALUES AS NUMERIC DATA!

Ray at work

 

[Giles]

Thanks very much Ray. I'll go and put on the spiky belt now..!.

"Ray Costanzo [MVP]"

 

[Slim]

I have a simular problem with a extra twist
I was using invoice numbers in ORDER BY converting the invoice number to
Integer

but them someone in accounts for some silly reason started overwriting the
invoice numbers with text

I used the row id instead as this is for the most part the same order, but
no always. some times invoice numbers are entered out of order, so the fix
is not perfect.

I assume I can use some sort of SQL statement like
CASE isnumeric(invoiceNumber )

 

[Bob Barrows [MVP]]

I have a simular problem with a extra twist
I was using invoice numbers in ORDER BY converting the invoice
number to Integer

but them someone in accounts for some silly reason started
overwriting the invoice numbers with text

I used the row id instead as this is for the most part the same
order, but no always. some times invoice numbers are entered out of
order, so the fix is not perfect.

Do you want character entries to appear before or after numeric entries?

I assume I can use some sort of SQL statement like
CASE isnumeric(invoiceNumber )

The original problem involved Access. I assume you are talking about SQL
Server? If so, then yes, CASE will be your solution, but I would avoid
ISNUMERIC (http://www.aspfaq.com/show.asp?id=2390). Assuming you want the
character entries to appear after the numeric entries:

ORDER BY
CASE WHEN
PATINDEX('%[^0-9]%',invoiceNumber)>0 THEN 999999999
ELSE CAST(invoiceNumber AS int) END,
invoiceNumber

Bob Barrows

 

[Slim]

Do you want character entries to appear before or after numeric entries?


The original problem involved Access. I assume you are talking about SQL
Server? If so, then yes, CASE will be your solution, but I would avoid
ISNUMERIC (http://www.aspfaq.com/show.asp?id=2390). Assuming you want the
character entries to appear after the numeric entries:

actually before would be better as we are using looking at the last few

thanks for this

ORDER BY
CASE WHEN
PATINDEX('%[^0-9]%',invoiceNumber)>0 THEN 999999999
ELSE CAST(invoiceNumber AS int) END,
invoiceNumber

Bob Barrows

would this give them to me before?

ORDER BY
CASE WHEN
CAST(invoiceNumber AS int)
ELSE PATINDEX('%[^0-9]%',invoiceNumber)>0 THEN 999999999 END,
invoiceNumber


thanks

 

[Bob Barrows [MVP]]

ORDER BY
CASE WHEN
PATINDEX('%[^0-9]%',invoiceNumber)>0 THEN 999999999
ELSE CAST(invoiceNumber AS int) END,
invoiceNumber

Bob Barrows

would this give them to me before?

ORDER BY
CASE WHEN
CAST(invoiceNumber AS int)
ELSE PATINDEX('%[^0-9]%',invoiceNumber)>0 THEN 999999999 END,
invoiceNumber

No, this will raise an error, which you would have discovered if you had
taken the 30 seconds to actually try it ... ;-)

This is how it would be done:
ORDER BY
CASE WHEN
PATINDEX('%[^0-9]%',invoiceNumber)=0 THEN 'zzzzzzzzzz'
ELSE invoiceNumber END,
CASE WHEN
PATINDEX('%[^0-9]%',invoiceNumber)>0 THEN -1
ELSE CAST(invoiceNumber AS int) END

Bob Barrows

 

[Slim]

ORDER BY
CASE WHEN
PATINDEX('%[^0-9]%',invoiceNumber)>0 THEN 999999999
ELSE CAST(invoiceNumber AS int) END,
invoiceNumber

Bob Barrows

would this give them to me before?

ORDER BY
CASE WHEN
CAST(invoiceNumber AS int)
ELSE PATINDEX('%[^0-9]%',invoiceNumber)>0 THEN 999999999 END,
invoiceNumber

No, this will raise an error, which you would have discovered if you had
taken the 30 seconds to actually try it ... ;-)

This is how it would be done:
ORDER BY
CASE WHEN
PATINDEX('%[^0-9]%',invoiceNumber)=0 THEN 'zzzzzzzzzz'
ELSE invoiceNumber END,
CASE WHEN
PATINDEX('%[^0-9]%',invoiceNumber)>0 THEN -1
ELSE CAST(invoiceNumber AS int) END

Bob Barrows

thank very much,