CODEWITHSUNDEEP
mysql
cwybr
cwybr

Reputation: 3

Deleting all rows except those with max date

If my schema is a table with id, uid & date like..

id uid date

1 10 2019-01-01 10:50:40

2 10 2019-01-02 09:20:20

3 11 2019-01-04 11:00:00

4 11 2019-01-04 08:30:30

Query looks like SELECT id, max(date) FROM table GROUP BY id;

And I returning 2 columns so get error Operand should contain 1 column(s) as expected.

How do I return single column and then delete all other rows like DELETE FROM table WHERE id NOT IN (SELECT id, max(date) FROM table GROUP BY id).

Thanks

EDIT:

I figured I can DELETE from table where id not in (SELECT id from (SELECT id, max(date) from table) a) ????

Upvotes: 0

Views: 1859

Answers (1)

Ed Bangga
Ed Bangga

Reputation: 13006

you can use subquery with NOT IN statement

delete from tableA where id not in 
(select t.id
    from (select id, max(date) maxd from tableA group by id)t)

Upvotes: 1

Related Questions