DELETE query - all but MAX of ID

[ks1392]

Hi folks, I am having trouble creating a delete query where I need to match on a number of customer criteria and delete all records but the MAX of the unique identifier for each grouped result. Here is a sample query:

select left(btn,6), campaignname, custname, count(*) quantity from repaircalls inner join campaigns on repaircalls.campaignid = campaigns.campaignid
group by left(btn,6), campaignname, custname
having count(*) > 1
order by quantity desc

This query returns to me a customer name, campaign name (this is for a callout list in a call center), the area code and exchange of a customer's telephone number, and a total count of records matching the group by clause. As soon as I add the unique ID field to the mix I obviously get 0 records returned because there's only one per record. I've tried enclosing this query in an exists clause to no avail.

Basically, I need to obtain the record IDs for these summarized records and delete all but the MAX(ID) for a given customer name, campaign name and Area Code / Prefix combination. Anyone have some pointers on how I could accomplish all this without cursors?

Thanks,
Kerry

 

[Pattycake245]

Wouldn't this do it?

select max([id]), left(btn,6), campaignname, custname, count(*) quantity from repaircalls inner join campaigns on repaircalls.campaignid = campaigns.campaignid
group by left(btn,6), campaignname, custname
having count(*) > 1
order by quantity desc

Tim

 

[ks1392]

Right, but how would I isolate the max(id) field so as to put it in an IN clause using something like the above query as a subquery to isolate out the max(id)?