delete rows that do not have min(rid)

selec开发者_如何学Ct min(q.rid)
    from qvalues q
        inner join batchinfo b
            on q.rowid = b.rowid
                and b.instrument = 'tf1'
    group by q.rowid, q.name, q.compound
    having count(*) > 1

1. instead of selecting the min(rid) how do i delete everything except for min(rid)?
2. how do i delete everything except for max(rid)?

please note that i want to delete only values that have the same rowid, name, and compound

---

begin transaction

delete from [table]
where rid != 
(select min(q.rid)
from qvalues q
    inner join batchinfo b
        on q.rowid = b.rowid
            and b.instrument = 'tf1'
group by q.rowid, q.name, q.compound
having count(*) > 1)
and rowid != 
(select q.rowid
from qvalues q
    inner join batchinfo b
        on q.rowid = b.rowid
            and b.instrument = 'tf1'
group by q.rowid, q.name, q.compound
having count(*) > 1)
and name != 
(select q.name
from qvalues q
    inner join batchinfo b
        on q.rowid = b.rowid
            and b.instrument = 'tf1'
group by q.rowid, q.name, q.compound
having count(*) > 1)
and compound != 
(select q.compound
from qvalues q
    inner join batchinfo b
        on q.rowid = b.rowid
            and b.instrument = 'tf1'
group by q.rowid, q.name, q.compound
having count(*) > 1)

to delete all but max, you do the same with the max syntax