Simple case-sensitive groupings

• BillyWilly

  SSCrazy

  Points: 2304

  More actions

  January 21, 2004 at 10:09 am

  #162955

  Hi-

  I'm in the U.S. so I leave all the collation settings to the default.

  However, now I want to group and count by a text field and case sensitivity is important.

  With this basic query:

  select

   [User],

   Subject,

  from

   TestTable

  group by

   [User],

   Subject

  If I have different case subject items - example:

  Item1

  item1

  they show up as one grouping.

  How can I VERY SIMPLY tell SQL Server to be case sensitive ONLY in the query analyzer and ONLY for one query.  I don't want any permanent DB collation changes made.

  Thanks - B

• Jonathan

  SSC-Insane

  Points: 20427

  More actions

  January 21, 2004 at 10:29 am

  #491006

  Not sure what your data type or collation is but, assuming it's varchar(10) and the default collation for US, then try:

  SELECT [User], CAST(Subject AS varchar(10)) COLLATE SQL_Latin1_General_CP1_CS_AS

  FROM TestTable

  GROUP BY [User], CAST(Subject AS varchar(10)) COLLATE SQL_Latin1_General_CP1_CS_AS

  
  
  --Jonathan