"Ambiguous column name 'Id'"

Shaan

4y826SQL

Hi everyone.
Doesn't it work with order by alias bound to the parameter?

My order by:

ORDER BY CASE
             WHEN @ordercolumn = 'Id' THEN Id
             WHEN @ordercolumn = 'Username' THEN Username
             END

I get error "Ambiguous column name 'Id'"

My select:

SELECT DISTINCT P.Id,
                P.Username     Username,
                                   ………

I can't post the whole code because it's 150 lines but there are no other Id columns in the selection

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Sachin Singh4y ago
Yes, you need to use alias (P) in the order by clause also
ORDER BY CASE
             WHEN @ordercolumn = 'Id' THEN P.Id
             WHEN @ordercolumn = 'Username' THEN P.Username
             END
+1
Rajanikant Hawaldar4y ago
Seems at least 2 of the tables involved in the selection have a column 'Id', even if not used in the selection, they are still considered. Need to be explicit from which table to use 'Id': WHEN @ordercolumn = 'Id' THEN P.Id
+1
Uday Dodiya4y ago
You Give Table Alias 'P'

So Please Try To Change Your Query As Per Following
ORDER BY CASE
             WHEN @ordercolumn = 'Id' THEN P.Id
             WHEN @ordercolumn = 'Username' THEN P.Username
             END
And Remove Alias From Column User Name Like Following
SELECT DISTINCT P.Id,
P.Username ,
+1