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