Let's say i have a column with various Values, now i want to find a Value that exists the most, how is the better way to make such query?
Is the best solution to do this way:
WITH t AS
(SELECT 'a' AS VALUE FROM Dual UNION ALL
SELECT 'b' FROM Dual UNION ALL
SELECT 'a' FROM Dual)
SELECT VALUE
FROM (SELECT t.Value, COUNT(*) AS Cnt
FROM t
GROUP BY t.Value
ORDER BY Cnt DESC
)
WHERE Rownum = 1
As you see that Value 'a' exists the most times in table T, so the query must output the 'a' and nothing more. If the number of occurrences of the 'a' and 'b' would be same, then query should output one of them, doesn't matter which one.
As you see query now doesn't seem to be well optimized, i think.
(Oracle 10g)