[mythtv-users] sql query to identify duplicates

[Leo Butler]

>   On 30/04/14 11:46 AM, Leo Butler wrote:
>   >>    From: "Michael T. Dean" <mtdean at thirdcontact.com>
>   >>    On 04/30/2014 09:47 AM, Leo Butler wrote:
>   >>    > Hi,
>   >>    >
>   >>    > Since changing over from an analogue (pvr-150) to digital (hdhr3-cc)
>   >>    > tuner, I have written a custom recording rule to re-record episodes.
>   >>    > I simply modified the custom rule to re-record sdtv recordings when
>   >>    > available as hdtv.
>   >>    >
>   >>    > I would like to write an sql query to identify duplicate recordings,
>   >>    > in order to delete the older recording. I am no sql guru, and my first
>   >>    > thought was to query the recorded.duplicate field--but this is set to
>   >>    > 1 for all recordings, so the name belies its purpose.
>   >>    >
>   >>    > Any suggestions?
>   >>
>   >>    http://www.mythtv.org/wiki/FindDuplicateRecordings , but ideally you'd
>   >>    modify it so it operates on all recordings and so that things like
>   >>    category can be specified in the command line or interactively--and
>   >>    especially so that deletes are confirmed interactively.  Lots of
>   >>    examples at http://www.mythtv.org/wiki/Category:Python_Scripts (those by
>   >>    Raymond Wagner being excellent examples of how to do things properly
>   >>    with the Python bindings he wrote :).
>   >>
>   >>    This would be a much better "whole" solution than using SQL (which can't
>   >>    do the rest--like delete and such).
>   >
>   > Mike, thanks for the comments, but I am looking for an sql query.
>   > I guess I'll need to rtfm.
>   >
>
>   Try something like this:
>
>   SELECT field, COUNT(field) FROM table GROUP BY field HAVING 
>   (COUNT(field) > 1);
>
>   for example ( querying the mysql database as root)
>
>   select user, count(user) from user group by user having (count(user) > 1);
>   +--------+-------------+
>   | user   | count(user) |
>   +--------+-------------+
>   | mythtv |           4 |
>   +--------+-------------+
>
>   ( Yes, the mysql database has a field called user, in a table called 
>   user!. And I have 4 entries for the mythtv user  at localhost, 127.0.0.1 
>   etc)

Thanks, Geoff. 

Leo