Error with DISTINCT constraint in SQL CE (not SQL CE 2005)

[Guest]

I am having some problems getting this query to work. When I remove the
DISTINCT constraint the query works great but it does not produce unique
results. When I add the DISTINCT constraint back into the query I get an SQL
CE error telling me that a column name is invalid.

Here is the query (with the DISTINCT constraint):

sqltext = "SELECT DISTINCT PestID,Pest FROM tblPests " & _
"WHERE (CustomerID = " & customerid & " " & _
"OR CustomerID = 1) " & _
"AND Active='Y' " & _
"AND (CustomerChemicalID = " &
spraying.chemicalid1 & " " & _
"OR CustomerChemicalID = " &
spraying.chemicalid2 & " " & _
"OR CustomerChemicalID = " &
spraying.chemicalid3 & ") " & _
"ORDER BY CustomerID DESC, Pest ASC;"

Does anyone have any suggestions on this or know what in the world that I am
doing wrong? I am quite sure it is syntax related.

Thanks in advance,
nb

 

[chris-s]

I think you'll find that in this case, you are required to include the
ORDER BY fields in the SELECT clause, eg "SELECT DISTINCT PestID, Pest,
CustomerID FROM...."

Chris

 

[Guest]

Well, that got rid of the invalid column problem but for some reason I am
still not getting distinct pest names. I tried swapping pestid and pest in
the select statement but it does not effect it.

Any thoughts on this one?

nb

 

[chris-s]

You can't do that, it will return the DISTINCT combinations of all
fields in the SELECT clause. Think about it and you will see why it
can't do it.

Chris

 

[Guest]

You are correct. After looking over some documentation that I found on the
net I realized that about an hour after I responded to your reply. Now, I
guess that I am going to have to come up with another solution to the problem.

Thanks for your help Chris.

nb