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. Advertising

  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

    "Giles" <> wrote in message
    news:%...
    > 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], Jan 5, 2006
    #2
    1. Advertising

  3. Giles

    Giles Guest

    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" <> wrote in message
    > news:%...
    >> 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
    #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 )







    "Ray Costanzo [MVP]" <my first name at lane 34 dot commercial> wrote in
    message news:...
    > 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" <> wrote in message
    > news:%...
    >> 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, Jan 5, 2006
    #4
  5. 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.
     
    Bob Barrows [MVP], Jan 5, 2006
    #5
  6. Giles

    Slim Guest

    "Bob Barrows [MVP]" <> wrote in message
    news:...
    > 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.
    >
    >
     
    Slim, Jan 6, 2006
    #6
  7. 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"
     
    Bob Barrows [MVP], Jan 6, 2006
    #7
  8. Giles

    Slim Guest

    "Bob Barrows [MVP]" <> wrote in message
    news:...
    > 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"
    >
     
    Slim, Jan 6, 2006
    #8
    1. Advertising

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Shixx
    Replies:
    0
    Views:
    928
    Shixx
    Jun 8, 2004
  2. Soren Kuula
    Replies:
    2
    Views:
    503
    Soren Kuula
    Feb 1, 2004
  3. cspoh
    Replies:
    0
    Views:
    252
    cspoh
    Jul 31, 2003
  4. Stephan Kämper
    Replies:
    2
    Views:
    239
    Stephan Kämper
    Jan 18, 2004
  5. rajesh
    Replies:
    0
    Views:
    178
    rajesh
    Nov 23, 2005
Loading...

Share This Page