Ambiguous sort column error
Hello,
I have a small SQL question.
Running an example queries in our Samples Namespace:
1. select top 5 Description,Category from Cinema.Film order by Category - runs fine no issues and returns 2 columns as expected
2. select top 5 * from Cinema.Film order by Category - runs fine no issues and returns 8 columns as expected
3. select top 5 Description,Category,* from Cinema.Film order by ID - runs fine no issues, and returns 10 columns, with my first 2 repeated
4. When I try to combine the first 2 queries:
select top 5 Description,Category,* from Cinema.Film order by Category
I get an error:
Ambiguous sort column: CATEGORY^ SELECT TOP ? Description , Category , * FROM Cinema . Film ORDER BY Category
I assume there is a simple explanation and it's not critical, but I would love to understand what is the issue. I kind of expect to get the similar 10 columns with my first 2 columns of my Description and Category repeated twice, but ordered by Category, similar to example #3.
Thank you
There is a simple explanation!
Your output will have two columns named Category, because you've specified it explicitly and it's also included in "*". So the query engine doesn't know which of these two columns you're asking to sort by.
Either remove the explicit specification for Category, or give it a unique name:
select category as Category2 ...
Thank you, that makes perfect sense.
Fun fact, you can also order by the column by number.
In this case:
select top 5 Description,Category,* from Cinema.Film order by 2
Thanks Tim, that works.