Wow, options popping out of the woodwork left and right! We'll probably try out several of these and see which suits best. Thanks a lot, everyone! Will On 2017-10-25 16:31, Ray Voelker wrote: > Figured I'd chime in with something I spent entirely way too much time > on > (probably). A JavaScript class to normalize and sort LC call numbers > > https://github.com/rayvoelker/js-loc-callnumbers > > Not sure if that helps you in your particular situation, but it might > give > you a place to start along with the other suggestions given! > > Good luck! > > --Ray > > On Oct 25, 2017 5:16 PM, "Craig Dietrich" <[log in to unmask]> > wrote: > >> 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 >> >> >>