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