1

I have a table like

table name : emailvalue

--------------------------------------
| email                    |   value |
-----------------------------------
|  [email protected]            |  A      |
--------------------------------------
|  [email protected]            |  B      |
--------------------------------------
|  [email protected]            |  C      |
--------------------------------------
|  [email protected]          |  F      |
--------------------------------------
|  [email protected]            |  G      |
--------------------------------------
|  [email protected]            |  A      |
--------------------------------------
|  [email protected]            |  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

--------------------------------------
|  [email protected]            |  B      |
--------------------------------------
|  [email protected]            |  C      |
--------------------------------------   

 and also        

 --------------------------------------
|  [email protected]            |  A      |
 --------------------------------------
|  [email protected]            |  H      |
--------------------------------------
Improve this question
4
  • 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. Commented Sep 22, 2013 at 6:37
  • See the table there are 3 row for email [email protected] . i want to delete last 2 row of those 3 row.. Commented Sep 22, 2013 at 6: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. Commented Sep 22, 2013 at 6:42
  • How do you build that order of our table? Commented Sep 22, 2013 at 6:43

3 Answers 3

Reset to default
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;
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

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
Improve this answer

9 Comments

Won't that only delete the one where value = C in OP's example?
no, it will delete all rows for each email where value is not the min(value) = A
Ah, yes, I see. Good job.
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..
@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?
|
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?

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.