Ambiguous sort column error

SQL, Caché

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

  • 0
  • 0
  • 51
  • 2
  • 2

Answers

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

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.