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

Could you describe what you are actually trying to achieve. This statement does not really make sense at all — user330315
– user330315, Commented Aug 17, 2010 at 21:09

OMG Ponies · Accepted Answer · 2010-08-17 20:06:52Z

3

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...

answered Aug 17, 2010 at 20:06

OMG Ponies

334k85 gold badges536 silver badges508 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user330315 Over a year ago

Actually all DBMS except MySQL will refuse to run such a query. MySQL simply returns indeterminate results

Dave Over a year ago

actually the statement was incorrect, we were not grouping by COLUMN_A but another column instead. actually what we want is this <code> 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 </code>

OMG Ponies Over a year ago

@a_horse_with_no_name: No, SQLite supports the same "hidden column" functionality that MySQL supports. But any real database, yes - you're correct. :)

OMG Ponies Over a year ago

@Dave: Update your question with example data, output from DESC table_xyz, and expected output.

user330315 · Accepted Answer · 2010-08-17 20:12:01Z

1

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

answered Aug 17, 2010 at 20:12

user330315

Comments

Shannon Severance · Accepted Answer · 2010-08-17 20:17:42Z

1

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.

answered Aug 17, 2010 at 20:17

Shannon Severance

18.5k4 gold badges49 silver badges67 bronze badges

Comments

JulesLt · Accepted Answer · 2010-08-17 21:35:11Z

1

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).

answered Aug 17, 2010 at 21:35

JulesLt

1,78510 silver badges10 bronze badges

Comments

Dave · Accepted Answer · 2010-08-17 20:12:05Z

0

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.

answered Aug 17, 2010 at 20:12

Dave

1,24413 silver badges24 bronze badges

Collectives™ on Stack Overflow

Query works in MySQL not Oracle

5 Answers 5

4 Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

4 Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related