Wow, options popping out of the woodwork left and right! We'll probably
try out several of these and see which suits best.
Thanks a lot, everyone!
Will
On 2017-10-25 16:31, Ray Voelker wrote:
> Figured I'd chime in with something I spent entirely way too much time
> on
> (probably). A JavaScript class to normalize and sort LC call numbers
>
> https://github.com/rayvoelker/js-loc-callnumbers
>
> Not sure if that helps you in your particular situation, but it might
> give
> you a place to start along with the other suggestions given!
>
> Good luck!
>
> --Ray
>
> On Oct 25, 2017 5:16 PM, "Craig Dietrich" <[log in to unmask]>
> wrote:
>
>> If you're using PHP you could use natsort() on the result,
>>
>> http://php.net/manual/en/function.natsort.php
>>
>> Alternatively, you could try ordering by the length of the VARCHAR
>> field
>> first,
>>
>> ORDER BY LENGTH(field), field
>>
>> Sent from mobile
>>
>> > On Oct 25, 2017, at 2:11 PM, Jodie Gambill <[log in to unmask]>
>> wrote:
>> >
>> > 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
>> >>
>>
|