Matches in a database.

[Von Fugal]

* Matthew Walker [Tue, 28 Jun 2005 at 11:34 -0600]
<quote>
> On Tue, 2005-06-28 at 11:20 -0600, C. Ed Felt 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.
> 
> I don't believe this is doable in a single query until MySQL 5.
> You /might/ be able to do it in 4.1, but I'm not sure.

It is doable, and not so hard. What's NOT doable is including step 4 in
"The One Query".

Some have given some ideas already, but to specifically return results
where there ARE duplicates of sessid, something like this...

select *, count(sessid) as cnt from CDR group by sessid having cnt > 1

that will give you all occurences where theres a duplicate sessid,
though you don't know _which_ duplicate row you will get.

But finding one query aside, why don't you just put a unique key on
sessid so there cannot be duplicates?

Von Fugal
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 189 bytes
Desc: Digital signature
Url : http://plug.org/pipermail/plug/attachments/20050628/a2117a94/attachment.bin