ORDER BY clause in SAP HANA different from SQL Server?

Question

I using an ORDER BY clause in SAP HANA:

SELECT a
FROM (
    SELECT 'r' AS A FROM DUMMY
    UNION
    SELECT 'V' FROM DUMMY
    UNION
    SELECT 'a' FROM DUMMY
    UNION
    SELECT 'A' FROM DUMMY
) a
ORDER BY A

In SAP HANA the result is:

A
A
V
a
r

But when running in SQL Server the result is:

A
a
A
r
V

Why is this ordering different? Can we access and see this setting at the database level in SAP HANA? How can I get the same sorting in HANA as I am getting in SQL Server?

One is case sensitive one is not. Change one to match the other as you prefer. — Dale K, May 18 '22 at 06:40
For case-insensitive ordering, based on English language, you may use `ORDER BY A COLLATE ENGLISH_CI` (suffix `CI` is for case-insensitive). The possible values are defined in the `COLLATIONS` system view. — Sandra Rossi, May 18 '22 at 07:18

score 5 · Answer 1 · edited Jul 04 '22 at 10:40

What you are looking for is the collation used when sorting the rows. A collation specifies what kind of sorting is applied to text strings. It is especially relevant for deciding how lower and uppercase characters are sorted and for how characters with diacritics are sorted.

In SAP HANA, you can specify a default collation for the whole database when creating the database, specify a collation for a table column when creating the table or specify a collation to use as part of the orderby clause.

In order to see what collations are available, check the COLLATIONS system view.

In order to specify the collation as part of the orderby clause, see this example:

 select * from mytable 
     order by col1 
     COLLATE TURKISH;

https://answers.sap.com/answers/12900664/view.html

ORDER BY clause in SAP HANA different from SQL Server?

1 Answers1