java - H2 DB - Column must be in Group By list -

i using h2-db access static databases...

i have table looks like:

country     state       city         lat     lng     countryid      stateid     cityid  "germany"   "berlin"    ""           1.23    1.23    1              1           0 "germany"   "münchen"   ""           1.23    1.23    1              2           0 "usa"       "alabama"   "auburn"     1.23    1.23    2              1           1 "usa"       "alabama"   "birmingham" 1.23    1.23    2              1           2 "usa"       "alaska"    "anchorage"  1.23    1.23    2              2           1 "usa"       "alaska"    "cordova"    1.23    1.23    2              2           2 

its huge list lots of countries, of them have country , state (like germany here, whereas state's cities), few have city (like usa here)...

the problem now, when query

select * mytable country = 'germany' group state order state 

to sorted list of states (or cities), error saying

field city must in group list 

if row has city, need whole row, otherwise need state column, can know after having queried it, wether uses city column or not, have query "*" instead of "state"

the query should okay, or? on mysql working... whats problem here?

found if helps: http://www.h2database.com/javadoc/org/h2/constant/errorcode.html#c90016

metin

mysql broken in regards this. allows columns in group by neither in group by nor arguments aggregation functions. in fact, documentation warns against using extension.

so can do:

select state diyanet country = 'germany' group state  order state; 

or this:

select state, min(city), min(lat), . . . diyanet country = 'germany' group state  order state; 

but select * not allowed , doesn't make sense.