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" <gi***@nospam.com> wrote in message
news:%2****************@TK2MSFTNGP10.phx.gbl...

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

[Giles]

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

"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" <gi***@nospam.com> wrote in message
news:%2****************@TK2MSFTNGP10.phx.gbl...

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

[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 )



"Ray Costanzo [MVP]" <my first name at lane 34 dot commercial> wrote in
message news:eI*************@TK2MSFTNGP14.phx.gbl...

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" <gi***@nospam.com> wrote in message
news:%2****************@TK2MSFTNGP10.phx.gbl...

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

[Bob Barrows [MVP]]

Slim wrote:

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
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Slim]

"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message
news:ev**************@TK2MSFTNGP09.phx.gbl...

Slim wrote:

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:

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
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Bob Barrows [MVP]]

Slim wrote:

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
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Slim]

"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message
news:OC**************@TK2MSFTNGP10.phx.gbl...

Slim wrote:

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,



--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"