 |
 |
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 > >> >