[RESOLVED] MS SQL - Group by

[Ecniv]

Simple quick question.

I`m using a simple group by and instead of getting the results I expect, MS Sql seems tso have merged the data, case insensitively.

eg.
RUA or Rua or rua all get put together as RUA (or whichever record it finds first).

I don`t remember having this problem in Access. If the data was different I got different lines.

Is it an easy fix? A setting on the MS Sql? an extra option in the SQL statement ?

[JuggaloBrotha]

Well, what's your query?
What's a sample of the data in the table(s)?

[szlamany]

You can try putting a CS (case sensitive) COLLATE on your query

COLLATE SQL_Latin1_General_CP1_CS_AS

You'll have to experiment with placement - or maybe make a temp table and define that column as a CS collate...

[Ecniv]

SQL is like :

SELECT afield
  FROM atable
GROUP BY afield

in the field i have data like : RUA, Rua, R, Av, Avenida, AVENIDA
the grouped result is : RUA, R, Av, AVENIDA

Szlamany: I'll have a try, thanks for the tip. Is the default usually case insensitive?

[szlamany]

...Szlamany: I'll have a try, thanks for the tip. Is the default usually case insensitive?

The default is however your server was installed - it's a site installation setting.

I've never seen a site that was installed as case-sensitive - that would be very interesting.

The only reason I know about the collate is because I had a user that upgraded SQL and selected a different collation then the database and sprocs were created with. I had to add all kinds of COLLATE's to TEMP TABLE's that referred to VARCHAR() fields. What a nightmare that was...

[Ecniv]

SELECT afield COLLATE SQL_Latin1_General_CP1_CS_AS tp
FROM atable
GROUP BY afield COLLATE SQL_Latin1_General_CP1_CS_AS
ORDER BY tp

The above works as I wanted/expected. Thanks Szlamany