CODEWITHSUNDEEP
androidsqlite
user1952115
user1952115

Reputation: 17

How can I delete all the duplicate rows exept one with maximum value?

id name exp

test01 park 500

test01 park 700

test01 park 3000

test01 park 1500

test02 kim 700

test02 kim 500

test03 lee 500

I want to delete all the duplicate rows except one with maximum exp value. I want to get the result like this:

id name exp

test01 park 3000

test02 kim 700

test03 lee 500

I tried some sql statements but failed. Is it possible to implement what I want with one sql statement? If so, how can I do that?

Upvotes: 0

Views: 91

Answers (2)

Barmaley
Barmaley

Reputation: 16363

Why delete? Just show only appropriate ones using query like this:

select id, name, max(exp) from mytable

Update: oops.. try this one:

select id, name, max(exp) from mytable group by id, name

It doesn't matter Android or not. For Android you can use: execSQL(String sql)

Upvotes: 2

josepn
josepn

Reputation: 367

Try this:

DELETE FROM mytable
  WHERE ROWID NOT IN ( SELECT t1.ROWID
                        FROM  mytable AS t1,
                              (SELECT id, name, MAX(exp) AS x FROM mytable GROUP BY id, name) AS t2
                          WHERE t1.id = t2.id AND t1.name = t2.name AND t1.exp = t2.x
                    );

Upvotes: 0

Related Questions