Writing Queries without the limit clause

Question

How can I rewrite the below query without the Limit clause?

SELECT project.title, 
       COUNT(project2manager.managerid) AS Cnt_Manager
FROM project2manager 
JOIN project ON project2manager.projectid = project.projectid 
GROUP BY project.title
ORDER BY Count_Manager DESC LIMIT 1;

+-----------------+-------------+
| project.title   | Cnt_Manager |
+-----------------+-------------+
| City Scape      |           8 |
+-----------------+-------------+

I tried to use the MAX clause but I keep getting the incorrect project title but the correct number of managers. what would be the best way to fix this?

SELECT title , MAX(Total) 
FROM (SELECT project.title, COUNT(project2manager.managerid) AS Total
FROM project2manager 
JOIN project ON project2manager.projectid = project.projectid 
GROUP BY project.title) AS Result;
+-------------------------+------------+
| project.title           | MAX(Total) |
+-------------------------+------------+
| Comic Con               |         8  |
+-------------------------+------------+

Sure, my question is unclear. For instance, if 2 rows have the same value then limit will only return one. I am unsure of what would be the best solution for this — jake jone, Feb 20 '20 at 17:42
Yes, that makes sense. Now you have two answers, depending on your MySQL version. — GMB, Feb 20 '20 at 17:54

score 0 · Answer 1 · answered Feb 20 '20 at 17:39

Try direct solution:

SELECT project.title, 
       COUNT(project2manager.managerid) AS Cnt_Manager
FROM project2manager 
JOIN project ON project2manager.projectid = project.projectid 
GROUP BY project.title
HAVING NOT EXISTS ( SELECT NULL
                    FROM project2manager p2m
                    JOIN project p ON p2m.projectid = p.projectid 
                    GROUP BY p.title
                    HAVING COUNT(p2m.managerid) >= Cnt_Manager )

For version 8+ do the same using CTE.

GMB · Accepted Answer · 2020-02-20T18:05:47.213

If you are running MySQL 8.0, you can handle top ties with rank():

select title, cnt_manager
from (
    select 
        p.title, 
        count(*) as cnt_manager,
        rank() over(order by count(*) desc) rn
    from project2manager pm
    join project p on pm.projectid = p.projectid 
    group by p.projectid, p.title
) t
where rn = 1

Note that I modified your query as follows:

unless there is a possibility that some records in project2manager have a managerid that is null, count(project2manager.managerid) can be shorten as just count(*)
table aliases make the query easier to write and read
it seems like you want to group data by project; if so, you should not realy solely on the project title for that - what if two different projects have the same title? To avoid that, I added projectid to the group by clause

Writing Queries without the limit clause

2 Answers2