Doing a case-sensitive GROUP BY query with MariaDB or MySQL

Fri, Aug 20, 2021

mysql case-sensitive group by
sql list items with same id
mariadb show only duplicates

Collate is frequently recommended; you only need to be sure you do the collate on the GROUP BY:

GROUP BY field_alpha_key_value COLLATE SQL_Latin1_General_CP1_CS_AS

Another option is to cast to binary:

GROUP BY CAST(field_alpha_key_value AS BINARY)

Or take an MD5 hash of the ID, which isn’t 100.000% guaranteed but should reduce false positives to zero:

GROUP BY MD5(field_alpha_key_value)

Complete query example:

SELECT entity_id, field_alpha_key_value, count(*)  AS c
FROM node__field_artist_key
GROUP BY field_alpha_key_value COLLATE utf8mb4_general_ci
HAVING c > 1
ORDER BY c DESC;