Menu
Under SQL Server. A table contains some text with different cases. I want to sort them case-sensitive and thought that a COLLATE in the ORDER BY would do it. It doesn't. Why?
CREATE TABLE T1 (C1 VARCHAR(20))
INSERT INTO T1 (C1) VALUES ('aaa1'), ('AAB2'), ('aba3')
SELECT * FROM T1 ORDER BY C1 COLLATE Latin1_General_CS_AS
SELECT * FROM T1 ORDER BY C1 COLLATE Latin1_General_CI_AS
Both queries return the same, even if the first one is "CS" for case-sensitive
aaa1
AAB2
aba3
(in the first case, I want AAB2, aaa1, aba3)
My server is a SQL Server Express 2008 (10.0.5500) and its default server collation is Latin1_General_CI_AS.
The collation of the database is Latin1_General_CI_AS too.
The result remains the same if I use SQL_Latin1_General_CP1_CS_AS in place of Latin1_General_CS_AS.
380
Collations in SQL Server provide sorting rules, case, and accent sensitivity properties for your data. Collations that are used with character data types, such as char and varchar, dictate the code page and corresponding characters that can be represented for that data type.
A collating sequence is a mapping between the code point and the requried position of each character in a sorted sequence. The numeric value of the position is called the weight of the character in the collating sequence. In the simplest collating sequence, the weights are identical to the code points.
Using Group By and Order By Together When combining the Group By and Order By clauses, it is important to bear in mind that, in terms of placement within a SELECT statement: The GROUP BY clause is placed after the WHERE clause. The GROUP BY clause is placed before the ORDER BY clause.
It will order the results by the first column, and then, if there are some rows with the same value in the first column it will order them by the second column.
Because that is the correct case sensitive collation sort order. It is explained in Case Sensitive Collation Sort Order why this is the case, it has to do with the Unicode specifications for sorting. aa will sort ahead of AA but AA will sort ahead of ab.
129
You need a binary collation for your desired sort order with A-Z sorted before a-z.
SELECT * FROM T1 ORDER BY C1 COLLATE Latin1_General_bin
The CS collation sorts aAbB ... zZ
42
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
