I want DISTINCT to return case sensitive...

[xvszero]

Is this possible? I'm trying to create a new report generator and it seems the old report generator was case sensitive, and because of this data was often entered that was similar except for case, yet considered separate data. Reports coming from my generator are not matching up, as they are not grabbing the data again if it is in a different case...

I found something about collating but I don't quite understand it...

COLLATE Latin1_General_CS_AS
I tried this...

SELECT DISTINCT x.name, x.enterdate, y.phone COLLATE Latin1_General_CS_AS FROM x INNER JOIN y ON (x.account=y.account) ORDER BY blfacct.name

But I got this error...

Unknown collation: 'Latin1_General_CS_AS'

Anyone?

[NogDog]

If MySQL, I think you want "latin1_general_cs". (I'm not sure if it matters if its all lower-case or not -- kind of ironic, eh?) See http://dev.mysql.com/doc/refman/5.0/en/charset-we-sets.html for more info.

[xvszero]

I'm not sure what I am doing wrong, but I got this error....

COLLATION 'latin1_general_cs' is not valid for CHARACTER SET 'binary'

when I set things up like this...

SELECT DISTINCT x.name, x.enterdate, y.phone COLLATE latin1_general_cs FROM x INNER JOIN y ON (x.account=y.account) ORDER BY x.name

[xvszero]

I think I may even be a bit confused on how DISTINCT works. All of these examples I keep finding seem to suggest it works on one field only, like...

SELECT DISTINCT name FROM employees

Etc. I was under the impression they acted by selecting distinct recordsets? Like...

SELECT DISTINCT name, phone FROM employees

would return

Bob, 999-999-9999
Frank, 999-999-9999
Bob, 111-111-1111

Etc. Am I correct, or have I been using DISTINCT wrong this whole time!?

[NogDog]

Try "latin1_bin" for the collation, perhaps?

DISTINCT applies to the entire row, not an individual field.

[xvszero]

'COLLATION 'latin1_bin' is not valid for CHARACTER SET 'binary''

My MYSQL server is 5.0.26 if that helps. Could it be that I have to change some settings on the server or something?!

[NogDog]

Maybe you could try setting your charset and collation in a separate "SET" statement, e.g.:

mysql_query("SET NAMES 'latin1' COLLATE 'latin1_bin'");

[xvszero]

Do you mean like before I actually call the query?

mysql_query("SET NAMES 'latin1' COLLATE 'latin1_bin'");
$query = $tempfilter;
$result = mysql_query($query) or die(mysql_error());
$row = mysql_fetch_array($result, MYSQL_ASSOC);

Etc.?

[NogDog]

Yes, as a separate query to tell the MySQL connection what "language" your following queries will be "speaking".