November 1, 201610 yr Hey all, I'm trying to get a php loop to display records descending by reference by using SELECT * FROM coll_grey order by ref desc But some numbers are into their thousands, so sorting by desc doesn't work. The results display something like this R997 R996 R995 R1002 Is there a way to get the 1002 to appear before the 997, so they are treated as real numbers? I guess it's only looking at the first few digits and maybe the R is causing a problem? Thanks guys
November 2, 201610 yr Sorry bud, SQL considers that a string and not a number, R9 comes before R1 in descending order, just the nature of the beast. Depending how many records you have depends what the best thing to do is. Might be worth making a varchar column for the prefix (e.g. R) and in int column for number (e.g. 1002). Then run a script to split the reference and store the prefix and int separately, but this really does depend on how much data there is, a CRON might be the better option for this. Edit: There's one way which is https://www.copterlabs.com/natural-sorting-in-mysql/ however it all depends if the prefix is always the same length. Edited November 2, 201610 yr by BrowserBugs
November 7, 201610 yr Author Sorry mate just seen your reply. Thanks for the link, it helped me get things working with... order by length(ref) desc, ref desc Nice one!
Create an account or sign in to comment