ORDER BY - "numerical string" problem

Discussion in 'ASP General' started by Giles, Jan 5, 2006.

  1. Giles

    Giles Guest

    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, Jan 5, 2006
    #1
    1. Advertisements

  2. 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
     
    Ray Costanzo [MVP], Jan 5, 2006
    #2
    1. Advertisements

  3. Giles

    Giles Guest

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

    "Ray Costanzo [MVP]"
     
    Giles, Jan 5, 2006
    #3
  4. Giles

    Slim Guest

    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 )
     
    Slim, Jan 5, 2006
    #4
  5. 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:

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

    Bob Barrows
     
    Bob Barrows [MVP], Jan 5, 2006
    #5
  6. Giles

    Slim Guest


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

    thanks for this



    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
     
    Slim, Jan 6, 2006
    #6
  7. 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
     
    Bob Barrows [MVP], Jan 6, 2006
    #7
  8. Giles

    Slim Guest

    thank very much,
     
    Slim, Jan 6, 2006
    #8
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.