Ferenjito Ferenjito - 1 month ago 6
MySQL Question

Group by -having statement from mysql to oracle

I have this statement, which works well in MySQL

SELECT r
FROM ( SELECT MAX(recno) AS r,
f1,
f2,
f3,
f4,
f5,
f6,
f7,
f8,
COUNT(*) AS count2
FROM mytable
GROUP BY f1,
f2,
f3,
f4,
f5,
f6,
f7,
f8
HAVING (count2 > 1)) tr


But does not work well in Oracle


ORA-00904: invalid identifier "count2"


How can I make it run in Oracle?

Answer

Just use

HAVING COUNT(*) > 1

as you can't use alias.

Comments