Issue with Collation in Mysql

Question

I have a table as

mysql> show create table tbl_name\G

************* 1. row *************

   Table: tbl_name

Create Table: CREATE TABLE `tbl_name` (
  `name` char(15) CHARACTER SET latin1 DEFAULT NULL,
  `id` int(5) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin7 COLLATE=latin7_estonian_cs

1 row in set (0.00 sec)

The DATA In Table is as

mysql> select * from tbl_name;

 name       id 
 manaf      1 
 manaf      2 
 MANAF      3 
 MAnaf      4 

4 rows in set (0.00 sec)

Now i want the Case Sensitivity in all the records

mysql> select distinct(name) from tbl_name;

It should return the 3 rows in result but it is returing just 1.why..?? As I have set the table collation as latin7_estonian_cs

Answer 1

try this

SELECT DISTINCT BINARY value FROM tableName

Reference