Complex query. Help me, please!

Question

I have a table type:

id | text  | tel  | time
----------------------------
 1 | text1 | tel1 | unixtime
 2 | text1 | tel1 | unixtime
 3 | text1 | tel1 | unixtime
 4 | text2 | tel2 | unixtime
 5 | text3 | tel3 | unixtime
 6 | text3 | tel3 | unixtime

I want it "clean", try to delete all rows where is text = tel, and leave one with the largest time. I began like this:

SELECT concat(`text`, `tel`),
       count(concat(`text`, `tel`)) 
FROM `ads` GROUP by concat(`text`, `tel`) 
Having count(concat(`text`, `tel`)) > 1

something as something works, but something all wrong: (but still I can not guess how to remove, leaving only a one record of repeating ... anyone have any ideas, please write!

Answer 1

SELECT text, tel, max(time)
FROM ads
WHERE text = tel
group by text, tel
having count(time) > 1

I may be missing something here ...

Answer 2

SELECT text, tel, MAX(time)
FROM ads
WHERE text=tel
GROUP BY text, tel

Answer 3

I think he misphrased the question, since according to his code snippet he's looking for rows where the combination of text+tel are the same (hence the CONCAT). He's not simply looking for rows where text=tel.

If you're willing to write a quick script for this and not keep it in pure SQL, you could solve it in two steps.

First, find the rows where text+tel are duplicated:

mysql> 
SELECT count(id) as dupe_count,
text as dupe_text,
tel as dupe_tel,
max(time) as dupe_maxtime 
FROM tbl 
GROUP BY concat(text,tel) 
HAVING dupe_count > 1;
+------------+-----------+----------+--------------+
| dupe_count | dupe_text | dupe_tel | dupe_maxtime |
+------------+-----------+----------+--------------+
|          4 | text1     | tel1     |   1285203547 | 
+------------+-----------+----------+--------------+
1 row in set (0.00 sec)

Then iterate through them, and use:

DELETE FROM tbl WHERE text=$2 AND tel=$3 AND time < $4;

You could also use the dupe_count result to do a "DELETE FROM tbl WHERE ... ORDER BY time ASC LIMIT x", where x is dupe_count-1 (effectively deleting all but one row from each dupe group).

With a few more minutes of work you could probably port the latter DELETE logic into a single step with a subquery -- but if you're just looking to run this once, I'd recommend scripting it, looping the results, running the deletes, and being finished.