SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Guru menuserve's Avatar
    Join Date
    Aug 2004
    Location
    florida
    Posts
    903
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Ordering Char/Number Combos...

    I am using the following piece in my SELECT statement... I'm grouping and ordering rows by a number that identifies the card.

    GROUP BY cardnumber, grade ORDER BY CAST(cardnumber AS UNSIGNED)

    My "cardnumber" column is not an INT as some numbers have letters in them. When the combo begins with a number and ends with a letter... 34T it works.

    However when I have cards numbered GG4 and GG5, its not ordering them correctly as GG5 shows in my table before GG4.

    Any help would be appreaciated.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by menuserve View Post
    My "cardnumber" column is not an INT as some numbers have letters in them.
    you can sort these simply by removing all the letters before the CAST

    CAST(REPLACE(REPLACE(cardnumber,'G',''),'T','') AS UNSIGNED)

    you mentioned only G and T -- if there are other letters, you need an additional nested REPLACE for each one

    if there are lotsadem, then my advice is to add an additional INTEGER column to the table, e.g. called intcardnumber, and populate it during the insert process with a stripped-down number, so that you can use this for sorting
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru menuserve's Avatar
    Join Date
    Aug 2004
    Location
    florida
    Posts
    903
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    For some reason, it also doesn't seem to group them. If i enter two rows in the DB with cardnumber of 5... it shows one row which is good. each item has a grade 1-10 so the row on my output lists them all in one row and tallys the count for each grade.

    When I have a cardnumber that has letters, it won't group or order them properly.

    Any way to have it do this? If there is a letter in front of the number, its always the same letter for that group.... TT1, TT2, TT3, etc. Can I tell it to ignore the TT when ordering & grouping?

  4. #4
    SitePoint Guru menuserve's Avatar
    Join Date
    Aug 2004
    Location
    florida
    Posts
    903
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    r937... going to try that today!

  5. #5
    SitePoint Guru menuserve's Avatar
    Join Date
    Aug 2004
    Location
    florida
    Posts
    903
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    r937, that worked like a charm!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •