My bad, DISTINCT... ORDER BY UPPER works indeed as one can test using set mode regular; with a as (select 'a', 'b') select distinct 1 from a order by upper(2)
As to the mode, I understand it's confusing. I am at the mercy of that legacy app which sends non-standard SQL except if it the driver identifies as Oracle. Hence I try different modes before considering forking h2 to fool the app in believe it's Oracle. Thanks Evgenij - "case closed" On Wednesday, May 1, 2019 at 8:25:04 PM UTC-4, Evgenij Ryazanov wrote: > > So you use either Oracle or H2 in MySQL compatibility mode? It's a pretty > strange choice, you're just looking for trouble. > > If your query looks like > SELECT DISTINCT A FROM someTable ORDER BY someFunction(A) > you don't need a compatibility mode, such query is accepted by H2 since > 1.4.198. (But don't use 1.4.198, use 1.4.199 instead.) > > If your query looks like > SELECT DISTINCT A FROM someTable ORDER BY B > it is obliviously invalid. Such queries aren't allowed by the standard and > all sane (in that specific area) databases don't allow them too. > > If there is only one value of B for each value of A you can include column > B to list of selected columns. > > If multiple values of B are possible for each distinct value of A, such > ORDER BY clause in meaningless and should be removed. > > Old versions of MySQL allow such invalid queries, so H2 also allows them > in MySQL mode only (since 1.4.198). More recent versions (since 5.7) of > MySQL don't allow them any more. > -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
