• Advertisement
Sign in to follow this  

[SQL] Distinct

This topic is 4577 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

Recommended Posts

I'm using MS SQL fyi. I know what Distinct is and what it does, returns only unique results, but I don't think it's working on this larger query I'm doing. I'm going to go ahead and post my query, but a lot of it is irrelivent, I'm posting it all in hopes you might be able to pinpoint why it's not returning only distinct results:
SELECT DISTINCT isnull(c1.[Contact],''),c1.[Company],c1.[Address1],c1.[City],c1.[State],c1.[Zip],c2.[latestlsapexpiredate],c3.[organizationname]
FROM [Contact1] c1
inner join Compulink.dbo.CustomerProfile c2 on c2.companyid = c1.key4 /* Gets LSAP */
inner join Compulink.dbo.CustomerName c3 on c2.varid = c3.companyid /* Get Var Names */
WHERE c1.[Address1] != 'NULL'
AND c1.[City] != 'NULL'
AND c1.[State] != 'NULL'
AND c1.[Zip] != 'NULL'
AND c1.[Zip] != '*'
AND c1.[State] != '*'
AND c2.[latestlsapexpiredate] >= '2/7/2005'
AND c2.[latestlsapexpiredate] <= '7/7/2005'
ORDER BY c1.[Company] ASC
Now all I'm trying to do here is pull mailing list information from our databases across various tables using JOIN. We have multiple contacts for a single company, but I only want one of them to be returned. Problem is, DISTINCT (if that is the problem even), isn't doing that. I get returned all the instances of a company with the contact. Is there some sort of unique() funciton that will only return a single result instance of a column with nondistinct values? Edit: Actually distinct is pulling a couple off the list, but still, the problem is there.

Share this post


Link to post
Share on other sites
Advertisement
I think the problem is that you assume DISTINCT is only working on the first column when actually it works on all the columns. If you have multiple c1.[Contact] but one of the other columns is different (probably c2.[latestlsapexpiredate]), you will get another row.

If you understand while the DISTINCT is working like it is, it might help with a solution. For example, if the c2.[latestlsapexpiredate] is the column causing you to get duplicate rows, which c2.[latestlsapexpiredate] value would you want if DISTINCT worked like you expected?

Share this post


Link to post
Share on other sites
Sorry about this, I figured out my entire problem was an entirely different issue. =/

Thanks though.

Share this post


Link to post
Share on other sites


On a side note, you are comparing columns with the string literal 'NULL'. NULL is not a string it is a value that is never equal to anything. To test for columns being non-NULL you should use "column-name IS NOT NULL".

Share this post


Link to post
Share on other sites
Sign in to follow this  

  • Advertisement