DISTINCT on another field than index

[DanielLiljeberg]

Hi

I have a query that look kind of like this

SELECT DISTINCT COUNT(*) as num, tblregion.sRegion AS region FROM tblintresse LEFT JOIN tblorter ON tblorter.sOrtname = tblintresse.Ort LEFT JOIN tblregion ON tblorter.cIdRegion = tblregion.cIdRegion WHERE tblregion.sRegion IN (1, 3, 5) AND Date >= '$startDate' AND Date <= '$endDate GROUP BY tblregion.sRegion

Now, the thing is that tblintresse has an id, and I think that if I count DISTINCT it will look for duplicates of that id and discard them. BUT I wish to select distincly on another field in tblintresse named company. How do I do this? I tried something like SELECT DISTINCT on tblintresse.company (beacuse it felt like that could have been what the syntax should be) but that didn't work 😛
So what is the code to select wich field to use when using DISTINCT?

[Sxooter]

What you want is something like:

select count(distinct fieldname) from ....

That's assuming your version of MySQL works properly. It certainly works in my installation, which is 5.0.19.