Sign in to follow this  
chbrules

[SQL] Distinct

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


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

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

Sign in to follow this