CODEWITHSUNDEEP
snowflake-cloud-data-platform
Jag Thind
Jag Thind

Reputation: 51

Snowflake Ordering on Text seems Incorrect

I am new to snowflake and have noticed the ordering on text columns does not behave as expected.

Take this simple example:

select *
    from ( values ('ab'), ('aBc'), ('acd') ) t(col1)
order by col1

Expected order: ab, aBc, acd

Actual order: aBc, ab, acd

Am I missing something?

Thank you.

Upvotes: 1

Views: 1573

Answers (3)

Saul Bonardi
Saul Bonardi

Reputation: 156

You could use COLLATE specification directly in the order by clause.

The collate lets you specify following configuration settings to be used when comparing values:

  • Locale
  • Case-sensitivity
  • Accent-sensitivity
  • Punctuation-sensitivity
  • First-letter preference
  • Case-conversion
  • Space-trimming

Following example uses English Locale(en) and Case Insensitive(ci) collation:

select *
    from ( values ('ab'), ('aBc'), ('acd'), ('Z') ) t(col1)
   order by collate(col1, 'en-ci');

Result returned:

ab aBc acd Z

Upvotes: 4

Greg Pavlik
Greg Pavlik

Reputation: 11046

This will also sort "Z" before "a" because it's first in ASCII / Unicode order. You can order by with an upper function:

select *
    from ( values ('ab'), ('aBc'), ('acd'), ('Z') ) t(col1)
order by col1

To sort without case sensitivity, you can use the upper or lower function.

select *
    from ( values ('ab'), ('aBc'), ('acd'), ('Z') ) t(col1)
order by upper(col1)

Upvotes: 0

Damião Martins
Damião Martins

Reputation: 1849

According to documentation:

All data is sorted according to the numeric byte value of each character in the ASCII table. UTF-8 encoding is supported.

In the ASCII table, B comes before b.

It's weird the order by didn't consider the string length in the ordering.

Upvotes: 0

Related Questions