CODEWITHSUNDEEP
mysqlsql
Prakash
Prakash

Reputation: 693

SQL : Delete from table where not max in group by

i have query like following

select max(id), count(id) 
from table 
group by col1, col2  
having count(col1)>1 and count(col2)>1;

which results output like below 

**+---------+-----------+
| max(id) | count(id) |
+---------+-----------+
|      43 |         4 |
|     108 |         2 |
|     171 |         2 |
|     177 |         2 |
|     195 |         2 |
+---------+-----------+**

which is used to find the duplicate records with the same col1 and col2 for multiple records

From this i have to delete all the records except max(id) record.

I have tried it like this 

Delete 
from payment_records 
group by policy_id, ref_txn_no  
having count(policy_id)>1 and count(ref_txn_no)>1 
where id !=(select max(id) 
from payment_records 
group by policy_id,ref_txn_no  
having count(policy_id)>1 and count(ref_txn_no)>1)

is it correct

Upvotes: 0

Views: 1498

Answers (4)

Henrik
Henrik

Reputation: 2921

The id should be unique. Therefore you can select the data rows to delete by the ids and you do not need grouping etc. The inner select returns multiple values, so you should use the IN statement.

The following code is expected to work, but it does not, because MySql cannot delete from the table nested in the where clause.

DELETE FROM payment_records 
WHERE NOT id IN (
        SELECT max(id)
        FROM payment_records 
        GROUP BY policy_id, ref_txn_no  
        HAVING count(policy_id) > 1 AND count(ref_txn_no) > 1
    )

But there is a workaround without creating a temporay table:

DELETE FROM payment_records 
WHERE NOT id IN (
        SELECT max_id
        FROM (
            SELECT max(id) max_id
            FROM payment_records 
            GROUP BY policy_id, ref_txn_no  
            HAVING count(policy_id) > 1 AND count(ref_txn_no) > 1
        ) AS t
    )

Upvotes: 2

Suvendu Shekhar Giri
Suvendu Shekhar Giri

Reputation: 1394

Try this. It resolved my problem similar to you. 

DELETE FROM table WHERE id NOT IN (
       SELECT MAX(id)
       FROM table
       GROUP BY col1,col2
       HAVING COUNT(col1)>1 and COUNT(col2)>1
       )

Note: I haven't tried this query in SSMS before posting but it should work, may be with little modification :)

Upvotes: 0

user4003407
user4003407

Reputation: 22132

As far as I know, MySQL does not allow select from same table in sub query for delete. You have to use temporary table.

create temporary table t as
select
    max(id)
from payments_records
group by
    policy_id,
    ref_txn_no;
delete from payments_record
where id not in (select * from t);
drop temporary table t;

Upvotes: 1

Dhaval
Dhaval

Reputation: 2379

I think this two statement doesn't required

---> group by policy_id, ref_txn_no
----> having count(policy_id)>1 and count(ref_txn_no)>1 

Delete 
from payment_records 
where id !=(select max(id) 
from payment_records 
group by policy_id,ref_txn_no  
having count(policy_id)>1 and count(ref_txn_no)>1)

Upvotes: 0

Related Questions