CODEWITHSUNDEEP
mysqlsqloracle-database
Lokomotywa
Lokomotywa

Reputation: 2844

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?

Upvotes: 0

Views: 53

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

You don't need the subquery:

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

There is nothing "Oracle-specific" about writing the query this way. It is just a simpler way to write the query that should work in any database.

Upvotes: 4

starko
starko

Reputation: 1149

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 (count(*) > 1)) tr

Upvotes: 0

Raphaël Althaus
Raphaël Althaus

Reputation: 60503

Just use

HAVING COUNT(*) > 1

as you can't use alias.

Upvotes: 4

Related Questions