Problems with SQL query. Select row with max() column

You need to get the username or usernames with the maximum number of integrations.

ER Diagramsenter image description here

Try One:

SELECT username 
FROM 
    (SELECT au.username, COUNT(DISTINCT ci.integration_name_id) AS max_ini 
     FROM auth_user au 
     RIGHT JOIN card_integrations as ci ON au.id = ci.user_id 
     GROUP BY au.username) T 
ORDER BY T.max_ini DESC 
LIMIT 1

Problems if there are several users with the maximum number of integrations.

Try Two (Scripts):

set @m = (SELECT MAX(i) FROM 
(SELECT au.username as u, COUNT(DISTINCT ci.integration_name_id) as i FROM auth_user au
RIGHT JOIN card_integrations as ci ON au.id = ci.user_id
GROUP BY au.username) T);

SELECT u FROM (SELECT au.username as u, COUNT(DISTINCT ci.integration_name_id) as i FROM auth_user au
RIGHT JOIN card_integrations as ci ON au.id = ci.user_id
GROUP BY au.username) T WHERE i = @m;

Not optimal

Please help me, sorry for the English.

Answer

I think you just want window functions:

SELECT username 
FROM (SELECT au.username, COUNT(DISTINCT ci.integration_name_id) as max_ini,
             RANK() OVER (ORDER BY COUNT(DISTINCT ci.integration_name_id) DESC) as seqnum
      FROM auth_user au JOIN
           card_integrations ci
           ON au.id = ci.user_id 
      GROUP BY au.username
     ) T 
WHERE seqnum = 1;

Note: Assuming that there is at least one “integration”, then an outer join is not needed.