snowflake - Order by not sorting correctly

user_7855290 0
Viewed 328

I am running a query in snowflake with a group by and order by clause and I notice that it is not ordering the first column in ascending order

select distinct columnA from table order by columnA

ColumnA
------------ + 
AMP 1
AMP 2
Aluminum
Apple

In the example, Aluminum should be the first row however, it falls in the third. Seems to me that there is an uppercase and lowercase prioritization in sorting. How will I be able to make row 3 be the first row?

snowflake-cloud-data-platform
1 Answers

Upper caps rank above lower caps.

This will sort then irrespective of capitalization:

with data as (select * from table(split_to_table('AMP 1
AMP 2
Aluminum
Apple', '\n')))

select distinct value
from data
order by lower(value);
user_1324380
Related