Trying to get the 2 min(count()) with Ties-CodePudding

I allow myself to write a thread regarding a query I'm trying to make for hours now. I'm trying to get the name of the friend (friend.first_name) who refuses the most proposed dates for events.

To do this I'm counting the number of proposed date and ORDER BY ASC.

SELECT COUNT(*) 'NbrProposedDate', f.FIRST_NAME, 
f.LAST_NAME, f.FRIEND_ID
FROM PROPOSES
NATURAL JOIN FRIEND f
GROUP BY f.FRIEND_ID
ORDER BY NbrProposedDate ASC
LIMIT 1;

However, this does not take into account TIES.

What I'm looking for is the following result :

Furthermore, I've seen something with FETCH FIRST 1 ROW WITH TIES, however it does not seem to work with MySQL (getting SQL syntax issue).

Finally, I've had found an alternative using a function :

-- Find Minimum Count
SELECT MIN(cnt) INTO @min FROM (SELECT COUNT(*) cnt FROM     PROPOSES NATURAL JOIN FRIEND f GROUP BY f.FRIEND_ID) t;

-- Show Friends with minimum count 
DROP VIEW IF EXISTS troublemaker;
CREATE VIEW troublemaker AS
SELECT FIRST_NAME, LAST_NAME
FROM PROPOSES p
JOIN (SELECT FRIEND.FRIEND_ID 
      FROM PROPOSES 
      NATURAL JOIN FRIEND 
      GROUP BY FRIEND.FRIEND_ID 
      HAVING COUNT(*) = @min) t
ON p.FRIEND_ID = t.FRIEND_ID
JOIN FRIEND ON t.FRIEND_ID = FRIEND.FRIEND_ID
ORDER BY p.FRIEND_ID ASC;

However, the issue is that, I need to put this into a view, but "View's SELECT contains a variable or parameter".

Therefore, I'm looking for another alternative or a solution to fix this issue.

P.S. : here is an MLD :

CodePudding user response：

View is not required, a query result could be used as subquery

SELECT 
  COUNT(*) NbrProposedDate, 
  MAX(f.FIRST_NAME) FIRST_NAME,
  MAX(f.LAST_NAME) LAST_NAME, 
  f.FRIEND_ID
FROM PROPOSES
NATURAL JOIN FRIEND f
GROUP BY f.FRIEND_ID
HAVING COUNT(*) = (
  SELECT COUNT(*) 
  FROM PROPOSES
  NATURAL JOIN FRIEND f
  GROUP BY f.FRIEND_ID
  ORDER BY COUNT(*)
  LIMIT 1
)

On MySQL 8 , it can also use RANK()

WITH ranks AS (
SELECT 
  COUNT(*) NbrProposedDate, 
  MAX(f.FIRST_NAME) FIRST_NAME,
  MAX(f.LAST_NAME) LAST_NAME, 
  f.FRIEND_ID,
  RANK() OVER (ORDER BY COUNT(*) ASC) rk
FROM PROPOSES
NATURAL JOIN FRIEND f
GROUP BY f.FRIEND_ID
)
SELECT * FROM ranks WHERE rk = 1