nested delete/sql statements

the following statement returns this:

select rowid,name,compound,COUNT(*) from qvalues where rowid in (select rowid from batchinfo where instrument='tf1')
group by rowid,name,compound
having COUNT(*)>1



rowid   name    compound    (No column name)
19300   QC1 ETG 2
203开发者_运维问答79   QC4 ETS 2
20686   QC2 ETG 2
19402   QC1 ETG 2
23605   QC1 ETG 2
21705   QC3 ETS 2
23678   QC1 ETG 2
18480   QC2 ETG 2

..... ...... .....

i have another table called qvalues:

rid name    compound    rt  response    finalConc   qvalue  rowid
229263  QC3 Hydromorphone   0.69    6228.00 82.53   98  4695
229264  QC3 Morphine    0.51    3168.00 119.89  99  4695
229265  QC3 Oxymorphone 0.57    2472.00 83.89   99  4695
229266  QC3 Benzoylecgonine 2.17    20439.25    85.99   95  4695
229267  QC3 Codeine 1.34    4829.00 82.85   96  4695
229268  QC3 Hydrocodone 1.58    12416.00    84.99   94  4695
229269  QC3 Oxycodone   1.47    5430.00 78.44   94  4695
229270  QC3 Buprenorphine   2.54    366.13  10.41   98  4695
229271  QC3 Mepbrobamate    2.50    1330.34 95.56   92  4695
229272  QC3 Methadone   2.68    85897.27    86.77   92  4695

what i need to do is take the combination of the ROWID,NAME,COMPOUND from the first set and keep only the first occurrence of it in the second set. i need to delete all other rows that have the combination of ROWID, NAME, and COMPOUND from the compound. i need to keep this combination where the RID is the smallest.

please let me know if i need to clarify anything

I'd create a table alias on the second set, grouping by Name, Compound, having MIN(rowid) and then join that to the first set.

Then delete everything not in that set using NOT IN.

So you want to delete everything that is in batchinfo except the record with the smallest rid ...

delete qvalues where rid in
    (select rid
    from qvalues 
    where rowid in (select rowid from batchinfo where instrument='tf1') 
    except
    select rid
    from (select rid=min(rid)
        from qvalues
        where rowid in (select rowid from batchinfo where instrument='tf1') 
        group by rowid,name,compound) x);

count>2 isn't necessary because with count=1 it is always the record with the smalles rid.