nested delete/sql statements

Question

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
20379   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

Please post the code you have written so far. People generally do not like to just write your code for you. As it is, this is a work description, not a question. — Mitch Wheat, Dec 13 '10 at 23:03
I think you may be asking this question too many times (http://stackoverflow.com/questions/4434118/select-statement-to-find-duplicates-on-certain-fields). Just use my answer from that question in a DELETE statement and you've got your solution. We should probably close this one... — Nick Vaccaro, Dec 13 '10 at 23:10

score 0 · Accepted Answer · answered Dec 14 '10 at 00:27

0

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.

answered Dec 14 '10 at 00:27

Sam

7,543
7
48
62

thank you very much this is a great idea can you help me implement it – JOE SKEET Dec 14 '10 at 18:16

score 0 · Answer 2 · answered Dec 14 '10 at 05:57

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.

@herrow Sorry, but there isn't any incorrect syntax (at least at SQL Server 2008 SP2) — Manfred Sorg, Dec 15 '10 at 08:46

nested delete/sql statements

2 Answers2