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