CODEWITHSUNDEEP
phpmysqlduplicates
laiqn
laiqn

Reputation: 53

MySQL Delete Rows With Duplicate and Similar Content

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

Upvotes: 2

Views: 114

Answers (2)

xkeshav
xkeshav

Reputation: 54084

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

Upvotes: 4

Dieepak
Dieepak

Reputation: 546

Try it. 

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

Upvotes: 0

Related Questions