Joseph
Joseph

Reputation: 522

snowflake - Order by not sorting correctly

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?

Upvotes: 1

Views: 2098

Answers (1)

Felipe Hoffa
Felipe Hoffa

Reputation: 59375

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);

Upvotes: 2

Related Questions