Looking to remove duplicates from this list

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Looking to remove duplicates from this list

Author

Topic

velnias2010
Posting Yak Master

125 Posts

Posted - 2011-04-05 : 11:42:33

SELECT DISTINCT
organisationVoluntary.inkexID, organisationVoluntary.orgLogoURL, organisationRegulatory.name, organisationVoluntary.orgStrapline,
organisationVoluntary.overviewURL, actionsTaken.dateAdded
FROM organisationVoluntary INNER JOIN
organisationRegulatory ON organisationVoluntary.inkexID = organisationRegulatory.inkexID INNER JOIN
actionsTaken ON organisationVoluntary.inkexID = actionsTaken.inkexID
WHERE (organisationVoluntary.orgLogoURL <> 'NULL') AND (organisationVoluntary.orgLogoURL <> '')
GROUP BY organisationVoluntary.inkexID, organisationVoluntary.orgLogoURL, organisationRegulatory.name, organisationVoluntary.orgStrapline,
organisationVoluntary.overviewURL, actionsTaken.dateAdded
ORDER BY actionsTaken.dateAdded DESC

Data Looks Like

7938 INKEx14032011125607.png IRISH NONPROFITS KNOWLEDGE EXCHANGE Action Inishowen Limited Strapline NULL 24/03/2011 14:37:43
7938 INKEx14032011125607.png IRISH NONPROFITS KNOWLEDGE EXCHANGE Test Strapline NULL 24/03/2011 14:37:43
1779 INKEx1403201112560715032011164820.png ACTION INISHOWEN LIMITED NULL 24/03/2011 12:43:03
7938 INKEx14032011125607.png IRISH NONPROFITS KNOWLEDGE EXCHANGE Action Inishowen Limited Strapline NULL 23/03/2011 15:14:55

The First Column is called inkexID, Basically I only want 1 row returned for each inkexID and this to have most recent dateAdded

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-04-05 : 12:08:43

Remove 'actionsTaken.dateAdded' from the group by..
and do
dateAdded = max(actionsTaken.dateAdded)
in the select...

Corey