Dear community,
I have to make a "simple" query on ORACLE 11g DB who will output ONLY numbers in field1 who exceeded a specific threshold.
In other words, assuming I have the following data in database.
FIELD1 FIELD2 FIELD3
========= ============== ==============
3291234567 333991123456789 1234
3277654321 333011123456789 9876
3481234567 333101123456789 1234
3291234567 333991123456789 1234
3291234567 333011123456789 1234
3277654321 333015123456789 9876
3277654321 333103123456789 9876
3277654321 333201123456789 9876
3481234567 333112123456789 1234
I want to output only number in field1 with occurrences >= 3, so the query output will be:
FIELD1 FIELD2 FIELD3
========= ============== ============
3277654321 333011123456789 9876
3277654321 333015123456789 9876
3277654321 333103123456789 9876
3277654321 333201123456789 9876
3291234567 333991123456789 1234
3291234567 333991123456789 1234
3291234567 333011123456789 1234
Doesn't matter the output order, the important thing is that only occurrences >= 3 based on FIELD1.
Now, this works perfect on MySQL:
SELECT A.FIELD1, A.FIELD2 , A.FIELD3
FROM
table A
INNER JOIN
(
SELECT FIELD1, COUNT (1)
FROM table
GROUP BY FIELD1
HAVING COUNT (1) >= 3
) AS B
ON A.FIELD1 = B.FIELD1
But on ORACLE 11g I got:
) AS B
*
ERROR at line 10:
ORA-00905: missing keyword
Please help!
Thanks
Lucas