SQL suggestion - whats wrong with the syntax here?-CodePudding

I cant seem to figure out what is wrong with the SQL here, when an asterisk (*) is included in the SELECT statement. eg:

SELECT firstname, lastname, createdon, * from Person
Where firstname like '%J%'
Order By createdon desc

I get 'Ambiguous column name 'CreatedOn'. error.

However without the (*) where its not returning the remainder of the columns it works fine.

Any suggestion or workaround?

CodePudding user response：

createdon is in the returned dataset twice, once from the explicit call, and once from the *. As such there are 2 columns with the name createdon and so ORDER BY createdon is ambiguous; do you want to order by the first column named createdon or the second one (the fact that they would have same value is irrelevant, as SQL Server just sees 2 columns with the same name).

Really, you shouldn't be using SELECT * and should be defining all your columns; I don't see a need to return 3 of columns twice. But what you can do if you "must" have 2 copies of the same column is to prefix the one in the ORDER BY with the table name/alias; this would then refer to the column "in" the table, not the one in the result set (of which there are 2). I use an alias here:

SELECT P.firstname,
       P.lastname,
       P.createdon,
       * --Replace this with the actual columns you need, don't be lazy
FROM dbo.Person P
WHERE P.firstname like '%J%'
ORDER BY P.createdon DESC;

CodePudding user response：

In your query, you are selecting "createdon" column twice and but ordering the results by "one of them. The SQL interpreter doesn't understand which "createdon" column it needs to sort the result and so Ambiguous column name is returned. You can solve this by using aliasing one of the "createdon" in the selected statement.

CodePudding user response：

im not sure but try with that

SELECT firstname, lastname, createdon createdon2, * from Person Where firstname like '%J%' Order By createdon desc