SQLTeam.com | Weblogs | Forums

Max() function question?

sql2012

#1
 so i  have to encounted  some question,i feel uncertain. as we know,max() function can adjust  the largest of 

option_field,such as char,numeric,date and so on.
my question blow:
select max(optionfield) from Orders ,"optionfield" is Chinese traditional.
When I judged the big hours, it was the first character of the alphabet.but i have a field ,such as '盒‘ 、’卷‘ 、'库'
and so on .Judging by the first character of the alphabet, the first character of the '盒’ is ‘h’ ,and the first charcter of the '卷' is 'j',and the first charcter of the '库‘ is ’k‘ ,adjust character, result as below:
k>j>h.
so sql server query result is :'卷’。
But it's not what you think?
my query result is:'盒‘.
Can you tell me ,why?,Tank you please.


#2

hi, can you provide which collation you used?


#3

it does not really goes by the 拼音. So you can't really say 盒 (he) comes before 卷 (juan). It is depends on the collation you selected. But i have no idea how is it internally works for various collation.


#4

now i know that you are not banana!


#5

of-course i am not :grinning:


#6

I think you need to use a Pinyin collation to get that sort order. Not sure what the normal sort order of Chinese collation is - might be the binary Character Value?? :frowning: but for a native looking in a dictionary I believe they would use stroke-count (there are Stroke collations available too)


#7

I don't think that is available, the last time i check there are only stroke, bin sort order. Well, that was quite a long time ago, while i was first working on SQL Server 2000. Maybe they have added that.

And i am surprise you know that 拼音 is pinyin. :open_mouth:


#8

Well I don't really ... I only speak Japanese, not Chinese :smile: and Japanese has a straightforward Kana which the Kanji can be written in (and I presume, compared to Chinese, that helps foreigners like me to grapple with it more easily). The Japanese also seem to be fairly at home with Romaji - which is the Roman-character equivalent of the Kana - e.g. envelopes addresses in Romaji will be reliably delivered by the Postman :smile:

The Kana "alphabet" has combinations of various consonants with all the vowels; the vowels can be used on their own (including a double/"long" form) but the only consonant which can be used on its own is "n". For Japanese that's about the Top & the Bottom of it ... much more complicated in Chinese I think??

As a foreigner learning Japanese there is one other "snag" in that there are two complete Kana alphabets. One (Hiragana) is used for Japanese words and the other (Katakana) for "Imported" words. As a foreigner I found this a nightmare because it is clear to all Japanese people that a particular word is "imported" because they only ever see it in print in Katankana, there are no equivalent Kanji, and, seeing my foreigner's-face, assumed that I quite clearly must be able to understand that word (regardless of the language it was "imported" from!!) and thus strangers would use them at every opportunity when speaking to me. The Japanes for "Bread" comes form the Portugese, for example ... . I'm dyslexic and tonally-similar words are a nightmare for me to "guess" the original, and restricting Western words to letter+vowel syllables means that they don't sound, to my ear, much like the original at all!

I spent a year or more seeing "Woman" signs (in Katakana) on the front of buses and could not work out what the heck it meant ... eventually I asked someone only to learn that it was actually a "One man" bus - with no ticket collector!! (They could have written it O-Ma-N, but that would have been pronounced "Oh-man" whereas "Wo-ma-n" sounds, I suppose?!!, more like "One-man" !!

So in Japanese it would be easy to use the Kana to get a true Western-alphabet sort order ... which I assume would be the same with pinyin?


#9

Found this in the DOCs

Chinese (Macao SAR) has Windows collations: Chinese_Traditional_Pinyin_100_ and Chinese_Traditional_Stroke_Order_100_

Chinese (Singapore) only has collation: Chinese_Simplified_Stroke_Order_100_

https://msdn.microsoft.com/en-us/library/ms188046.aspx


#10

Well that's out of date! I did (on SQL2012) :

SELECT	* 
FROM	fn_helpcollations()
WHERE	name LIKE '%Chinese%'
ORDER BY name

and there are clearly both Stroke and Pinyin available for several flavours

Hong Kong, PRC, Taiwan - only Stroke
Simplified and Traditional - Pinyin and Stroke
Taiwan also has a Bopomofo / Zhuyin collation which I expect would also solve this problem?


#11

thank you ,i had solve it. i use chinese_prc


#12

yes, it relatives to collation.:smile:


#13

thank you .the reason why as you said.


#14

thank you .:sunny:


#15

Kristen u chinese? :open_mouth:


#16

Nope, just Double Dutch most of the time :smile:


#17

Firstly, my apology to @johnson for hijacking this thread :grinning:

I have learn a lot from @Kristen for all those years i am in SQLTeam. This is one of those time that i have to google to find out what is "double dutch". :open_mouth: And this i really have to clarify, with @Kristen you are referring to the jump rope game right ?


#18

Hahaha ... NO!! Too old for that :smile: but rather this Double Dutch definition

Maybe, but I doubt you learnt any SQL from me, whereas over the years I have often had the opportunity to admire and learn from the tight-code that you produce so effortlessly :smile: