Print

Print


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