MySQL Delete Rows With Duplicate and Similar Content

Question

I am new for PHP and MYSQL. In my study I met a problem

I have a table like this:

+----+----------+---------+
| time | name   | number  |
+----+----------+---------+
| 12.1 | google | 10      |
| 12.2 | yahoo  | 15      |
| 12.3 | msn    | 20      |
| 12.1 | google | 10      |
| 12.1 | google | 29      |
| 12.2 | yahoo  | 10      |
+----+----------+---------+

but I want the talbe like this:

+----+----------+---------+
| time | name   | number  |
+----+----------+---------+
| 12.2 | yahoo  | 15      |
| 12.3 | msn    | 20      |
| 12.1 | google | 29      |
+----+----------+---------+

when the time and the name are the same, I want the row with the max number,

what should I do? I am very worrying about this problem and thank you for anwsing me

Where's your code to do that? We can help you with your code, but we can't write it for you — ulentini
– ulentini, Commented Mar 29, 2013 at 8:42

xkeshav · Accepted Answer · 2013-03-29 08:44:44Z

4

TRY ( not tested )

SELECT `time`, `name`, `number`
FROM tbl
GROUP BY `name`,`time`
HAVING MAX(`number`)

tip : TIME is a mysql reserve keyword so wrap it with ` and create Index on name and time column together

answered Mar 29, 2013 at 8:44

xkeshav

54.2k47 gold badges183 silver badges255 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

laiqn Over a year ago

Excellent solution. I had tried it and it worked perfectly. Thank you very much. now I think I can continue my work.

Dieepak · Accepted Answer · 2013-03-29 08:44:41Z

0

Try it.

 Select time, name, number from [table name] group by time, name having max(number)

answered Mar 29, 2013 at 8:44

Dieepak

5462 silver badges7 bronze badges

Collectives™ on Stack Overflow

MySQL Delete Rows With Duplicate and Similar Content

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related