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

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
 
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
 
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
 
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
 
Back
Top