Will -- I use this sortLC php script that I ported from Michael Doran's perl version:
PHP: https://github.com/kenirwin/Weeding-Helper/blob/master/sortLC.php
Perl: https://rocky.uta.edu/doran/sortlc/
They have their flaws, but I find them to work pretty well.
I hope this helps!
Ken
-----Original Message-----
From: Code for Libraries [mailto:[log in to unmask]] On Behalf Of Will Martin
Sent: Wednesday, October 25, 2017 4:56 PM
To: [log in to unmask]
Subject: [CODE4LIB] Sorting LC call numbers in MySQL
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
|