How to do a case sensitive GROUP BY?
You need to cast the text as binary (or use a case-sensitive collation).
With temp as( select 'Test' as name UNION ALL select 'TEST' UNION ALL select 'test' UNION ALL select 'tester' UNION ALL select 'tester')Select Name, COUNT(name)From tempGroup By Name, Cast(name As varbinary(100))Using a collation:
Select Name Collate SQL_Latin1_General_CP1_CS_AS, COUNT(name)From tempGroup By Name Collate SQL_Latin1_General_CP1_CS_AS
You can use an case sensitive collation:
with temp as( select 'Test' COLLATE Latin1_General_CS_AS as name UNION ALL select 'TEST' UNION ALL select 'test' UNION ALL select 'tester' UNION ALL select 'tester')SELECT name, COUNT(name)FROM tempgroup by name
Simply:
SELECT count(*), CAST(lastname as BINARY) AS lastname_cs FROM names GROUP BY lastname_cs;