Finding the least frequest value in a column WITHOUT ORDER BY SQL MariaDB-CodePudding

I have a schema of enrolled students in a level, the question is I have to find which level has the least number of students which are in year '2000/01' and with program id 'G500' BUT, I can't use ORDER BY or LIMIT. This is the best answer I could come up with.

SELECT lvl
FROM   enrl
WHERE  ayr = '2000/01'
       AND pid = 'G500'
GROUP  BY lvl
ORDER  BY Count(*) DESC;

CodePudding user response：

WITH cte AS (
    SELECT lvl, COUNT(*) as count
    FROM   enrl
    WHERE  ayr = '2000/01'
           AND pid = 'G500'
    GROUP  BY lvl
)
SELECT t1.lvl FROM cte AS t1
LEFT OUTER JOIN cte AS t2 ON t1.count < t2.count
WHERE t2.count IS NULL;