Thanks again for the quick response 🙂

It almost does what I need, except for ONE thing: currently your code also deletes this row from prospectsincampaigns:

135501048

But that shouldnt be deleted...

Believe me, Im trying to describe my requirement as best as possible, my mistake if I missed something :$ For me: am not a big fan of surprises unless its...

I'm having real difficulties with the rule: all duplicate records where result=0 should be deleted, but at least 1 record should remain even if the result of that record is...

update (am working on it), the first statement would be:

SELECT MIN(p.id) id, p.companyname,c.campaignid FROM [dbo].[prospects] p

JOIN [dbo].[ProspectsInCampaigns] c ON c.prospectid = p.id

GROUP BY c.campaignid, p.companyname

HAVING COUNT(p.companyname)>1

This gives me...

Hi,

One final question with which I hope you are able to help me 🙂

Basically the statement you gave me consists of 3 separate statements. I've placed them below and placed...

Ah, I see, perfect, it now works! Thanks! 🙂

It does! 🙂 Thanks!

Thanks! 🙂

No. The objecttype value is already in the spotlights table [spotlights].objecttype and should determine which table should be joined.

The entire query must be able to execute without passing any parameters.

Thanks!...

Thanks, will have a look at this.

Also thanks for the tips, I will definitely discuss this with my client.

Ok, thats good advice.:)

The thing is that my client needs the functionality fast, so while Im remodeling (which has impact on other functionality too), I have to work with what...

My client wants to import records and then based on for example distribute some with a specific importcode to a certain campaign.

If I would import the records and immediately remove...

The natural key of prospects is "id", more records might hold the same companyname value. The situation where multiple DUPLICATE records with same companyname are in the prospects table is...

Ok, thx, I looked at you example page. I now have created this:

SELECT companyname, RANK() OVER (PARTITION BY companyname ORDER BY num) rnk

FROM (SELECT *, ROW_NUMBER() OVER(ORDER BY companyname) num

...

Guys, thank you both!!

@lowell: I did what you suggested, imported it into a temp table first 🙂