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 h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at https://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
