{question}
How do I sort by case sensitive in Single Store?
with bb as (
select 'AAA' as blip
union all
select 'aa'
union all
select 'aaa'
union all
select 'Aa'
union all
select 'aA'
union all
select 'AA'
union all
select 'Aa'
union all
select 'aA'
)
select
bb.blip as case_sensitive,
lower(bb.blip) as case_insensitive
from bb
order by case_sensitive ;
+----------------+------------------+
| case_sensitive | case_insensitive |
+----------------+------------------+
| aa | aa |
| Aa | aa |
| aA | aa |
| AA | aa |
| Aa | aa |
| aA | aa |
| AAA | aaa |
| aaa | aaa |
+----------------+------------------+
{question}
{answer}
The constant strings receive collation or character encoding rules as set by the collation_server.
In SingleStore, collation_server is utf8_general_ci, by default. Therefore, you are by default using case insensitive commands, and both columns have case insensitive comparisons.
To change to case-sensitive, the collation_server can be set to utf8_bin.
The following is an example to change to case-sensitive:
with bb as (
select 'AAA' as blip
union all
select 'aa'
union all
select 'aaa'
union all
select 'Aa'
union all
select 'aA'
union all
select 'AA'
union all
select 'Aa'
union all
select 'aA'
)
select
bb.blip:> text collate utf8_bin as case_sensitive,
bb.blip as case_insensitive
from bb
order by case_sensitive ;
+----------------+------------------+
| case_sensitive | case_insensitive |
+----------------+------------------+
| AA | AA |
| AAA | AAA |
| Aa | Aa |
| Aa | Aa |
| aA | aA |
| aA | aA |
| aa | aa |
| aaa | aaa |
+----------------+------------------+
Note: The lower command function is not needed if the collation is set to utf8_general_ci.
{answer}