 |
 |
If you're using PHP you could use natsort() on the result, http://php.net/manual/en/function.natsort.php Alternatively, you could try ordering by the length of the VARCHAR field first, ORDER BY LENGTH(field), field Sent from mobile > On Oct 25, 2017, at 2:11 PM, Jodie Gambill <[log in to unmask]> wrote: > > Hi Will - > I had a similar task a few years ago on a small project, though we only > used the classMark and classNum (from your example) parts of the call > number for what we needed. I implemented it as you outlined above, with two > separate fields to enable proper sorting -- classMark as varchar and > classNum as float. I look forward to hearing if anyone else has done it > more efficiently! > -Jodie > >> On Wed, Oct 25, 2017 at 3:55 PM, Will Martin <[log in to unmask]> wrote: >> >> We have a small web app with a MySQL backend, containing lists of books >> that have been reported lost and tracking our efforts at locating them. >> Access Services requested that the list of currently missing books be >> sorted according to LC call number. So we did, but the results are ordered >> in that uniquely digital "1000 comes before 2" way. For example, human >> logic would sort these seven call numbers like so: >> >> HM132 .A343 >> HM251 .M2 1960 >> HM278 .S37 1990 >> HM281 .C6713 1985 >> HM281 .H6 1958 >> HM1126 .F56 2011 >> HM1261 .K64 1978 >> >> But MySQL sorts them like so: >> >> HM1126 .F56 2011 >> HM1261 .K64 1978 >> HM132 .A343 >> HM251 .M2 1960 >> HM278 .S37 1990 >> HM281 .C6713 1985 >> HM281 .H6 1958 >> >> Currently, these are stored in a VARCHAR column in the MySQL table. The >> research that I've done suggests that there is no way just sort that column >> using ORDER BY, and that we'll have to split out the call number into >> separate columns for its component parts: >> >> HM = classMark >> 251 = classNum >> .M2 = cutter >> 1960 = pubYear >> >> ... and then do something like "ORDER BY classMark, classNum, cutter, >> pubYear". >> >> This database is unlikely to grow beyond a few hundred active records at >> most, so I'm not too concerned about the computational overhead of doing >> all that sorting. But it's going to be a fair bit of work to modify the >> table definition and then rejigger all the associated SQL queries in the >> app. >> >> Does anyone have a better solution for sorting LC call numbers in MySQL >> results? >> >> Thanks. >> >> Will Martin >> >> Head of Digital Initiatives, Systems & Services >> Chester Fritz Library >> University of North Dakota >>