cancel
Showing results forĀ 
Search instead forĀ 
Did you mean:Ā 
Data Engineering
cancel
Showing results forĀ 
Search instead forĀ 
Did you mean:Ā 

Query with distinct sort and alias produces error column not found

Kotofosonline
New Contributor III

Iā€™m trying to use sql query on azure-databricks with distinct sort and aliases

SELECT DISTINCT album.ArtistId AS my_alias 
    FROM album ORDER BY album.ArtistId

The problem is that if I add an alias then I can not use not aliased name in the order by clause.

ORDER BY album.ArtistId part produces an error. ORDER BY my_alias works.

If I remove distinct it also works.

Seems like after Project step original column name is lost. That behavior is unexpected for SQL compared to other SQL dialects. And I can not find any documentation about it.

Error in SQL statement: AnalysisException: cannot resolve '`album.ArtistId`' given input columns: [my_alias]; line 2 pos 22;
    'Sort ['album.ArtistId ASC NULLS FIRST], true
    +- Distinct
       +- Project [ArtistId#2506 AS my_alias#2500]
          +- SubqueryAlias spark_catalog.chinook.album
             +- Relation[AlbumId#2504,Title#2505,ArtistId#2506] parquet

Is there any way to make this query run as is, without modification maybe by changing some execution flags?

The query is generated by sqlachemy and works on other databases fine, I prefer to not edit it for databricks.

The schema used for testing is Chinook database.

The where clause works fine

SELECT DISTINCT album.ArtistId AS my_alias 
    FROM album WHERE album.ArtistId = 1

1 ACCEPTED SOLUTION

Accepted Solutions

User16752246494
Contributor

Hi ,

can you please try with the following.

%sql SELECT DISTINCT album.ArtistId AS my_alias 
    FROM album ORDER BY 1 desc

 or

%sql SELECT DISTINCT album.ArtistId AS my_alias 
    FROM album ORDER BY my_alias desc

View solution in original post

3 REPLIES 3

Kaniz
Community Manager
Community Manager

Hi @ Kotofosonline! My name is Kaniz, and I'm the technical moderator here. Great to meet you, and thanks for your question! Let's see if your peers on the community have an answer to your question first. Or else I will follow up shortly with a response.

User16752246494
Contributor

Hi ,

can you please try with the following.

%sql SELECT DISTINCT album.ArtistId AS my_alias 
    FROM album ORDER BY 1 desc

 or

%sql SELECT DISTINCT album.ArtistId AS my_alias 
    FROM album ORDER BY my_alias desc

Kotofosonline
New Contributor III

The code from above is worked in both cases.

Welcome to Databricks Community: Lets learn, network and celebrate together

Join our fast-growing data practitioner and expert community of 80K+ members, ready to discover, help and collaborate together while making meaningful connections. 

Click here to register and join today! 

Engage in exciting technical discussions, join a group with your peers and meet our Featured Members.