I have a table “employee” having the following data:
[TABLE=“class: grid, width: 500, align: center”]
[TR]
[TD]id
[/TD]
[TD]name
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]subhajit
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]ramesh
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]SuBhajit
[/TD]
[/TR]
[/TABLE]
I want to find the duplicate rows in the table. Thus I need a query which will return
[TABLE=“class: grid, width: 500, align: center”]
[TR]
[TD]id
[/TD]
[TD]name
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]subhajit
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]SuBhajit
[/TD]
[/TR]
[/TABLE]
Note that two “subhajit” are of different cases.
Please help me write the query. Thanks in advance.
You will need to play around with the character set and collation that has been setup upon creation of the table. I suspect that you have a case insensitive collation setup, hence, your results. You can either change the collation type in the table or change your query to convert the collation type in the group section of the query as follows:
mysql> create table toto (id int auto_increment primary key, name varchar(100) character set latin1 collate latin1_general_ci);
Query OK, 0 rows affected (0.03 sec)
mysql> insert into toto (name) values ('Ron'),('tOm'),('Tom'),('Tim'),('TOM'),('RON'),('Ron'),('Tim'),('Tom');
Query OK, 9 rows affected (0.00 sec)
Records: 9 Duplicates: 0 Warnings: 0
mysql> select * from toto;
+----+------+
| id | name |
+----+------+
| 1 | Ron |
| 2 | tOm |
| 3 | Tom |
| 4 | Tim |
| 5 | TOM |
| 6 | RON |
| 7 | Ron |
| 8 | Tim |
| 9 | Tom |
+----+------+
9 rows in set (0.00 sec)
mysql> select max(id), name from toto group by name;
+---------+------+
| max(id) | name |
+---------+------+
| 7 | Ron |
| 8 | Tim |
| 9 | tOm |
+---------+------+
3 rows in set (0.01 sec)
mysql> select max(id), name from toto group by name collate latin1_general_cs;
+---------+------+
| max(id) | name |
+---------+------+
| 6 | RON |
| 7 | Ron |
| 8 | Tim |
| 5 | TOM |
| 9 | Tom |
| 2 | tOm |
+---------+------+
6 rows in set (0.00 sec)