Matches in a database.

Jacob Fugal lukfugl at gmail.com
Tue Jun 28 12:21:36 MDT 2005


On 6/28/05, C. Ed Felt <edfelt at gmail.com> wrote:
> So, in short, (since a paragraph will get confusing):
> 
> 1.  SELECT all records in the CDR table after a requested date (usually
> 24 hours).
> 2.  Store these records in a huge array.
> 3.  Find all repeats on the 'sessid' field and store this in an array.
> 4.  Delete all repeats (save one copy of each repeat CDR).
> 
> Is there a MySQL query, (version 3), to select all rows that have one or
> more matching rows on a specific field ('sessid')?  This would
> essentially combine steps 1, 2 and 3 in one MySQL query.

DISCLAIMER: I haven't been able to test the following, as I don't have
mysql installed on this box, and don't want to go through the trouble.
However, I remember doing similar things, and according to my memory,
this might work...

SELECT unique_id, sessid, COUNT(*) AS n
FROM table WITH date_column > ?
GROUP BY sessid HAVING n > 1

and then looping over the results:

DELETE FROM table
WHERE sessid=?
AND NOT unique_id = ?

This solution assumes the existence of a unique ID field so that you
can distinguish records. Otherwise there's no way (in the query) to
exclude on of the duplicate records from the DELETE.

Jacob Fugal



More information about the PLUG mailing list