Query works in MySQL not Oracle

Question

The following SQL statement works in MySQL but not with Oracle:

SELECT *, MAX(COLUMN_A) 
  FROM table_xyz 
 WHERE COLUMN_A <= 100 
GROUP BY COLUMN_A

Oracle complaint: "FROM keyword not found where expected"

---

actually the statement was incorrect, we were not grouping by COLUMN_A but another column instead. actually what we want is this

SELECT *, MAX(COLUMN_A) 
  FROM table_xyz 
 WHERE COLUMN_A <= 100 
GROUP BY COLUMN_B

this works but gives us only column A and B

SELECT COLUMN_B, MAX(COLUMN_A) 
  FROM table_xyz 
 WHERE COLUMN_A <= 100 
GROUP BY COLUMN_B

what we want is this, but it doesn't work (group by error)

SELECT COLUMN_B, COLUMN_C .... COLUMN_X, MAX(COLUMN_A) 
  FROM table_xyz 
 WHERE COLUMN_A <= 100 
GROUP BY COLUMN_B

Answer 1

That's because Oracle requires you to define all the columns not wrapped in an aggregate function (MIN, MAX, COUNT, etc). SQL Server would return a similar error. MySQL's behavior is documented here.

Because your query is using SELECT *, I can't re-write it properly for you. But I also can't guarantee a syntactically correct version would return the same results as you see on MySQL either. Grouping by the same column you want the MAX is quite odd...

Answer 2

If you want the max() for column_a you don't need the group by at all:

SELECT MAX(COLUMN_A) 
  FROM table_xyz 
 WHERE COLUMN_A <= 100 

Answer 3

Multiple problems. Your GROUP BY clause is backwards. You need to define your GROUP BY by the columns in the *. Also what OMG Ponies said before.

Answer 4

In addition to what everyone else is saying, Oracle does not allow mixing * with explicit column definitions in queries:

SQL> select *, table_name from user_tables;
select *, table_name from user_tables
        *
ERROR at line 1:
ORA-00923: FROM keyword not found where expected

Oracle hasn't even looked at the fact that you are trying to get columns outside of those included in the group by clause. Which as others have stated, Oracle will not do.

Answer 5

This doesn't answer your MAX issue, but the only way to follow a '*' with other columns is if you use an explicit reference to a table alias - e.g.

 SELECT e.*, zip_code
 FROM  addresses a,
       employees e
 WHERE e.addressId = a.Id

For the MAX value, you will either need to group by all other columns, or look into analytic functions (plenty of previous answers on Stack Overflow).