Order By on a single table raises Ambiguous column name error

  • How in the world can the below query for a singular table produce the error msg "Ambiguous column name hMy"? The error goes away if I

    A) Alias th3 table and prefix the hMy column with that alias

    B) change SELECT so it's either just hMy or just *

    I understand how the ambiguous error can come into play with multiple table join query but how does this happen with a single table? How can it be ambiguous?  I can't provide the DDL for the table for NDA reasons but I can tell you that there is only 1 column named hMy and this is a regular table and not a view or Table valued UDF or the like.

    SELECT hMy, *
    FROM dbo.WF_HEADER
    WHERE 1=1
    ORDER BY hMy

    Kindest Regards,

    Just say No to Facebook!
  • Because * SELECTs all columns, which would include hMy.  Try this instead:

    SELECT hMy, *
    FROM dbo.WF_HEADER
    WHERE 1=1
    ORDER BY 1 --<<--

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • The actual issue is this:

    SELECT hMy, *

    Why might you ask because hMy also appears within the * listing so which hMy are you attempting to sort on?  That is ambigious.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply