[mythtv-users] sql query to identify duplicates
R. G. Newbury
newbury at mandamus.org
Wed Apr 30 16:41:25 UTC 2014
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)
Geoff
--
R. Geoffrey Newbury
More information about the mythtv-users
mailing list