 |
 |
The best way is probably to normalize the call numbers into a sortable string outside of MySQ, save that string to a sortable_callnumber field in your database, and sort by that. Normalizing call numbers ( http://robotlibrarian.billdueber.com/2008/11/normalizing-loc-call-numbers-for-sorting/) turns inherently unsortable call number strings into longer absolutely sortable strings, e.g: - E 184 .A1 G78 ==> E 0184.0000A 1000G 7800 - E184.A2 G78 1967 ==> E 0184.0000A 2000G 7800 1967 You will probably want to do this outside of MySQL. There are tools out there to do this: - https://github.com/libraryhackers/library-callnumber-lc - https://github.com/pulibrary/lcsort - https://metacpan.org/pod/distribution/Biblio-LCC/script/lccnorm You might need to make adjustments to match local call number practices. Once you've got the normalized call numbers you can just add a single field to your schema and sort by that. Ben On Wed, Oct 25, 2017 at 4: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 > -- Ben Florin Web Developer Boston College Libraries 617-552-4582 [log in to unmask]