Query help - removing random records

[gbrian]

I am having trouble figuring this one out. To make my question simple, let me give an example:

I have a database with 100,000 records, 2 fields (COMPANY, CONTACT TITLE). What I need to do is this:

1) Keep everyone that has a title.
2) Keep 2 individuals per company that do not have a title.

I am having problems with #2.

Any ideas?

 

[Crowley16]

if that's all the information you have, then it'd be pretty complicated to delete the required records because you can't really specify random records like that in jet (at least I don't think so, but correct me if I'm wrong)

if however you had a contactID field or something that you can use to explicitely specify the records to select, you can probably construct something to delete those records based on the value of Contact Title...

something like:
delete from tblName where ContactID NOT IN (SELECT TOP 2 ContactID where not isnull(ContactTitle) group by company)


--------------------
Procrastinate Now!

 

[gbrian]

I do have a contact ID. I was just trying to make the example simple--based on your example I am trying this:

SELECT TOP 2 DATA.CONTACT_ID
FROM DATA
WHERE IsNull(DATA.CONTACT_TITLE_LINE)
GROUP BY DATA.ORIGINAL_SITE_NAME

And it tells me CONTACT_ID is not part of the aggregate function.

 

[gbrian]

Bump...

I need to keep 2 contacts per company... and delete the rest. I have a Contact ID, Site ID, and each site may have from 1-500 contacts..


Thanks for any help.