[mythtv] Collective MySQL wisdom please

[Robin Hill]

On Sat Mar 12, 2022 at 12:20:03PM +0100, Roland Ernst wrote:

> It looks like one of the latest commits for MythWeb causes errors raised by
> MySQl server:
> See https://github.com/MythTV/mythweb/commit/d2aa1925 and the forum entry
> https://forum.mythtv.org/viewtopic.php?f=36&t=4797
> 
> Neither
> 
> SELECT UNIQUE(people.name)
> 
> nor
> SELECT DISTINCT people.name  ... ORDER BY credits.priority;
> 
> works with MysQL 8.0 and probably earlier versions.
> 
> Since I do not have the 'people' and 'credits' table populated, it is
> hard for me to test.
> 
> Roland

The query doesn't make a lot of sense as is - you can have the same
person appearing with multiple credits and priorities, so the
distinct/unique and order by conflict, which is presumably what's
causing the error. I'd suggest something like below instead:

SELECT a.name from (
SELECT people.name name, max(credits.priority) prio
FROM credits, people
WHERE credits.person = people.person
AND credits.role = 'actor'
GROUP BY people.name
) a ORDER BY a.prio;

That will return a unique set of names in order of their maximum
priority (if there's case issues, you may want to group by
upper(people.name) instead).

Cheers,
    Robin
-- 
     ___        
    ( ' }     |       Robin Hill        <myth at robinhill.me.uk>  |
   / / )      | Little Jim says ....                            |
  // !!       |      "He fallen in de water !!"                 |