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
|