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.
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
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?
r937… going to try that today!
r937, that worked like a charm!