Question: 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 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.

```
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

```

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

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

Answer: 
When tried, I also got the same error.

As a workaround

1. You can give the column position value (number) in the **Order By** clause.

```
select distinct teacher_id, name, age, id as student_id 
from student 
order by 4

```

[![enter image description here](https://i.stack.imgur.com/GdPEB.png)](https://i.stack.imgur.com/GdPEB.png)

2. You can give the column **alias** name in the Order By.

```
select distinct teacher_id, name, age, id as student_id 
from student 
order by student_id

```

Please feel free to post your question in [Microsoft Q&A](https://learn.microsoft.com/en-us/answers/questions/ask.html) forum where the product team will monitor them closely.
I ended up just disabling column aliases, but keeping table name aliases. So queries with self join looks like this:

```
select c2.id from Customer as c1 join Customer as c2 on c1.id = c2.id

```

No aliases and no problems with self joins.