Wow, interesting discussion. I see a lot of explanations that miss the ball by a long shot (hopefully, some of them were posted in jest!), some that almost or even completely hit the mark, but then add some incorrect sauce. I did not read every bit of every reaction, so maybe the actual true answer has been posted and I have overlooked it.
First, let's dispell some myths.
1. Sort order has nothing to do with singular or plural forms of the words, or in fact with any aspect of the meaning at all. For ordering purposes, string values are just a bunch of characters, whosse meaning is unimportant.
2. SOUNDEX() is not used in any way for ordering. The SOUNDEX() function is intended as a help for finding matches when a name may have been misheard (think: callcenter).
3. The "LATIN" in the collation name has nothing to do with the extinct Latin language. It refers to the latin alphabet, which is the alphabet used in a.o. Anglo-saxon languages. Special collations are required for countries that have different alphabets - even when the difference is too subtle to see. For Chinese and Arab alphabets, the difference is obvious, but there are also different rules for the "a, b, c" alphabet we use here. For instance, Germans have a special character "ß", which can also be represented as "ss", and which sorts (if I recall correctly) between s and t. So the German collations ensure that both ß and ss sort at that location, and that words with those different spelling compare as equal. Similarly, "ae" and "oe" are considered as equal to "ä" and "ö", with their appropriate sort locations. In some Scandinavion languages, however, the same "ae" and "oe" translate to other special characters: "æ" and "ø".
So, how does sorting work?
Most collations (I think all except the binary collations, but not 100% sure) sort in what's called "dictionary order". That is not related to whether the strings would actually be in a dictionary (see myth 1 above), but to how they would be ordered if they were in a dictionary or encyclopaedia. That is always the first rule. If you look up something in a dictionary, or in a (paper) encyclopaedia, you just check the palce of the term in the alphabet, without considering upper- or lowercase. If you'd want to look up "Netherlands", you'd expect to find it somewhere between "nether" and "nethermore", even though "Netherlands" is capitalized and the other words are not.
Also, in dictionaries, if a word is the same as another word but with a suffix, the word with suffix comes later. (In computer terms, this is implemented by padding the shorter words with spaces and ensuring the space character sorts first). So you expect "but" before "butter".
Finally, at the last position, case sensitivity comes into play. In a case insensitive collation, "pepper", "PEPPER" and "pEpPeR" are all the same word; they compare equal and their relative sort order is undefined. In a case sensitive collation, they are considered not to be equal, and a choice had to be made as to their relative order when sorting. The choice made was that lowercase comes before uppercase. Why? No idea - at some point in time, someone somewhere had to make a choice, and this is what came out of it.
I hope this helps!