CODEWITHSUNDEEP
sqlsql-serversql-server-2008group-by
Abe Miessler
Abe Miessler

Reputation: 85056

How to do a case sensitive GROUP BY?

If I execute the code below:

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 temp
group by name

It returns the results:

TEST   3
tester 2

Is there a way to have the group by be case sensitive so that the results would be:

Test   1
TEST   1
test   1
tester 2

Upvotes: 47

Views: 48313

Answers (5)

Alessandro
Alessandro

Reputation: 585

This works on my case:

SELECT BINARY example FROM table GROUP BY BINARY example;

Upvotes: 3

David Vielhuber
David Vielhuber

Reputation: 3569

In MySQL/MariaDB, if you don't want to use collations or casting to binary, just use:

SELECT MAX(name), COUNT(name)
FROM (
  select 'Test' as name
  UNION ALL
  select 'TEST'
  UNION ALL
  select 'test'
  UNION ALL
  select 'test'
  UNION ALL
  select 'tester'
  UNION ALL
  select 'tester'
) as tmp
group by MD5(name)

Upvotes: 3

Jordan DjMo
Jordan DjMo

Reputation: 331

Simply:

SELECT count(*), CAST(lastname as BINARY) AS lastname_cs 
FROM names 
GROUP BY lastname_cs;

Upvotes: 23

Lamak
Lamak

Reputation: 70648

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 temp
group by name

Upvotes: 31

Thomas
Thomas

Reputation: 64645

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 temp
Group By Name, Cast(name As varbinary(100))

Using a collation:

Select Name Collate SQL_Latin1_General_CP1_CS_AS, COUNT(name)
From temp
Group By Name Collate SQL_Latin1_General_CP1_CS_AS

Upvotes: 40

Related Questions