9

i am using H2-DB to access static databases...

i have a table which 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 a huge list with lots of countries, most of them just have Country and State (like Germany here, whereas State's are Cities), a few also have a City (like USA here)...

the problem is now, when i query

SELECT * FROM MyTable WHERE COUNTRY = 'Germany' group by STATE order by STATE

to get a sorted list of the states (or cities), i get an error saying 

Field CITY must be in the GROUP BY list

if the row has a city, i need the whole row, otherwise i would only need the State column, but i can just know after having queried it, wether it uses the city column or not, so i have to query "*" instead of "STATE"

the query should be okay, or? On MySql it is properly working... so whats the problem here?

Found this if it helps: http://www.h2database.com/javadoc/org/h2/constant/ErrorCode.html#c90016

Metin

jasiek.miko
  • 471
  • 6
  • 12
metinkale38
  • 718
  • 3
  • 10
  • 28
  • do you mean `SELECT STATE, CITY FROM DIYANET WHERE COUNTRY = 'Germany' ORDER BY STATE, CITY` – wero Jul 23 '15 at 19:35
  • Please edit your question with your desired results. – Gordon Linoff Jul 23 '15 at 19:42
  • If there is a city, I need "*" (would work, if there wouldn't be multiple empty city columns), if city is empty I do only need state... On mysql it is no problem, if there are multiple of one column (here they are always empty), it just gives one, I haven't to add that to group by... – metinkale38 Jul 23 '15 at 19:58

1 Answers1

8

MySQL is broken in regards to this. It allows columns in the GROUP BY that are neither in the group by nor arguments to aggregation functions. In fact, the documentation warns against using this extension.

So you can do:

SELECT state
FROM DIYANET
WHERE COUNTRY = 'Germany'
GROUP BY STATE 
ORDER BY STATE;

Or something like this:

SELECT state, min(city), min(lat), . . .
FROM DIYANET
WHERE COUNTRY = 'Germany'
GROUP BY STATE 
ORDER BY STATE;

But SELECT * is not allowed and doesn't really make sense.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Even if it nonsense, I need it :) but thank you, now I know the problem, why it is working on mysql and not on h2...I will add one query more, to fix it :)... – metinkale38 Jul 23 '15 at 20:00
  • But if I execute MySQL query directly, MySQL returns 'CITY' column which appears first in each group. I just need this behavior. Why would Hibernate forbidden this? – Wood Oct 24 '22 at 03:26