As stated in the title, suppose I have spreadsheet with following values:
B
C
A
Š
Ť
Á
I then import the values from this spreadsheet using QUERY and IMPORTRANGE to different spreadsheet, exact function:
=QUERY(IMPORTRANGE("<spreadsheet_id>"; "List 1!A:B"); "SELECT * WHERE Col1!='' ORDER BY Col1";-1)
The result I get is this:
A
B
C
Á
Š
Ť
So the ORDER BY works on separately on the non-accented letters and on the accented ones but not together, the result I expect is:
A
Á
B
C
Š
Ť
This makes it useless for me. When I try to order them manually the data gets distorted (move to completely different rows, the ordering is not correct either. I've check, that the language in the settings is set correctly.
When I've googled a little, some links addressing different SQL (like MySQL) COLLATE UNICODE, but this does not work.
Any ideas?
The issue is that query invokes a completely different language that doesn't necessarily behave like other Sheets functions such as sort. Since sort does what you want, use it instead of order by in query:
=SORT(QUERY(IMPORTRANGE(...); "SELECT * WHERE Col1!=''); 1; True)
sorts the output of query by the first column (1), ascending (True). The output is
A
Á
B
C
Š
Ť