CODEWITHSUNDEEP
sqlsql-server-2008sql-order-by
Radu Gheorghiu
Radu Gheorghiu

Reputation: 20489

ORDER BY works only with column alias

So, I have this query

SELECT
    NOTES,
    DATECREATED,
    DATEMODIFIED,
    * 
FROM myTable
WHERE
    USERCREATEDBY = 465
    AND NOTES LIKE ' :%'
ORDER BY DATECREATED

and it throws this error Ambiguous column name 'DATECREATED'.

I see nothing wrong with my query and I found a fix for this in the form of adding an alias to column DATECREATED, like DATECREATED a, and ordering by alias, ORDER BY a.

I do not understand why this happens and I am very curious to know why.

Upvotes: 3

Views: 148

Answers (5)

AnandPhadke
AnandPhadke

Reputation: 13486

try this:

SELECT
    NOTES,
    DATECREATED,
    DATEMODIFIED,
    * 
FROM myTable
WHERE
    USERCREATEDBY = 465
    AND NOTES LIKE ' :%'
ORDER BY 2

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 452977

Because DATECREATED appears in the SELECT list twice.

In this case both instances refer to the same underlying column but it is still invalid syntax. In general you could have two projected columns with the same name that refer to different columns. 

SELECT
    DATECREATED AS D,
    DATEMODIFIED AS D
FROM myTable
ORDER BY D /*Obviously ambiguous*/

By the way applying any sort of expression to the ORDER BY DATECREATED resolves the ambiguity as it will then be resolved against the column in the base table.

CREATE TABLE myTable
(
NOTES VARCHAR(50),
DATECREATED DATETIME,
DATEMODIFIED DATETIME
)


SELECT
    NOTES,
    DATECREATED,
    DATEMODIFIED,
    * 
FROM myTable
ORDER BY DATECREATED + 0 /*Works fine*/

I just mention this in passing however. It is definitely not a suggestion that you should do this.

Upvotes: 3

Andrey Gordeev
Andrey Gordeev

Reputation: 32449

It seems ORDER BY can't understand which DATECREATED column should be used for ordering. You specified it twice in SELECT clause.

Upvotes: 1

marc_s
marc_s

Reputation: 754258

Well, if your table contains this column DATECREATED, then you're selecting this column twice with the above SELECT - once explicitly, once implicitly by using the *.

So your result set now contains two columns called DATECREATED - which one do you want ORDER BY to be applied to??

Either explicitly specify the complete column list that you need, or then at least provide an alias for the DATECREATED column:

SELECT
    NOTES,
    SecondDateCreated = DATECREATED,
    DATEMODIFIED,
    * 
FROM myTable
WHERE
    USERCREATEDBY = 465
    AND NOTES LIKE ' :%'
ORDER BY DATECREATED

Upvotes: 3

vc 74
vc 74

Reputation: 38179

It's probably because you're selecting DATECREATED twice, once as a column and once in *

SELECT *
FROM myTable
WHERE
  USERCREATEDBY = 465
  AND NOTES LIKE ' :%'
ORDER BY DATECREATED

Should work fine

Upvotes: 5

Related Questions