Want to delete multiple row but not first row for same property in mysql?

Question

I have a table like

table name : emailvalue

--------------------------------------
| email                    |   value |
-----------------------------------
|  ab@gmail.com            |  A      |
--------------------------------------
|  ab@gmail.com            |  B      |
--------------------------------------
|  ab@gmail.com            |  C      |
--------------------------------------
|  cccc@gmail.com          |  F      |
--------------------------------------
|  dd@gmail.com            |  G      |
--------------------------------------
|  dd@gmail.com            |  A      |
--------------------------------------
|  dd@gmail.com            |  H      |
--------------------------------------

I want to delete all for same email without first row. how i will right the sql in my sql

the SQL should delete this 2 row in mysql

--------------------------------------
|  ab@gmail.com            |  B      |
--------------------------------------
|  ab@gmail.com            |  C      |
--------------------------------------   

 and also        

 --------------------------------------
|  dd@gmail.com            |  A      |
 --------------------------------------
|  dd@gmail.com            |  H      |
--------------------------------------

There is no such thing as a *first row* in relational databases. Tabales are *multisets* (somtimes called *bags*) of records, not *lists* of records. Keeping this in mind, please clarify what you mean by *first row*. — Oswald, Sep 22 '13 at 06:37
See the table there are 3 row for email ab@gmail.com . i want to delete last 2 row of those 3 row.. — Sarwar Hasan, Sep 22 '13 at 06:40
I told you there is no such thing as a *first row*. Likewise, there is no such thing as *last 2 rows*. You have to find some other criterion on which to base the descision about which records to remove. — Oswald, Sep 22 '13 at 06:42

MD SHAHIDUL ISLAM · Accepted Answer · 2013-09-22T08:18:49.727

3

Use it:

DELETE t1 FROM emailvalue t1
LEFT JOIN 
(
  SELECT email, value
  FROM emailvalue
  GROUP BY email
) t2 on t2.email = t1.email AND t2.value = t1.value
WHERE t2.value is null;

edited Sep 22 '13 at 08:18

answered Sep 22 '13 at 07:43

MD SHAHIDUL ISLAM

14,325
6
82
89

juergen d · Answer 2 · 2013-09-22T07:45:06.737

2

delete t
from your_table t
left join 
(
  select email, min(value) as minv
  from your_table
  group by email
) x on x.email = t.email and x.minv = t.value
where x.minv is null

edited Sep 22 '13 at 07:45

answered Sep 22 '13 at 06:39

juergen d

201,996
37
293
362

Won't that only delete the one where value = C in OP's example? – d'alar'cop Sep 22 '13 at 06:40
no, it will delete all rows for each email where value is not the min(value) = A – juergen d Sep 22 '13 at 06:41
Ah, yes, I see. Good job. – d'alar'cop Sep 22 '13 at 06:41
there is no dependency on value just need to delete from 2 row for a specific email please see the second example of the table.. – Sarwar Hasan Sep 22 '13 at 06:46
@SarwarCSE: What you don't understand is that there is no second element until you order your data by something. Do you have an id column or how do you know what is the first and what the second element? – juergen d Sep 22 '13 at 06:48
@juergend ok if there is another property named ID with auto increment then ? – Sarwar Hasan Sep 22 '13 at 06:56
@SarwarCSE: Is that id the order? Is the first entry the entry with the lowest id you want to keep? – juergen d Sep 22 '13 at 06:57
@juergend this sql show a error which is [Err] 1054 - Unknown column 'x.value' in 'where clause' – Sarwar Hasan Sep 22 '13 at 07:43
@SarwarCSE: Of course. Replace with `x.minv`. – juergen d Sep 22 '13 at 07:45

score 1 · Answer 3 · edited May 23 '17 at 12:22

1

ALTER IGNORE TABLE `emailvalue` ADD UNIQUE INDEX(`email`);

MySQL remove duplicates from big database quick

MySQL: ALTER IGNORE TABLE ADD UNIQUE, what will be truncated?

edited May 23 '17 at 12:22

Community

1
1

answered Sep 22 '13 at 06:44

Ning

76
3

Want to delete multiple row but not first row for same property in mysql?

3 Answers3