February 8, 2008 at 9:15 pm
while running below statement in SQL server 2005 getting error message
Ambiguous column name 'firstname'.
select distinct firstname,firstname from customers
order by firstname
February 9, 2008 at 2:50 am
Whilst it's clear to you that the two firstNames are the same, it's not quite so clear cut to MS SQL Server. You have two columns called FirstName being returned (which is a bad idea in the first place) and then asking MS SQL Server to order by the Firstname column in the result. There's two of them so it's ambiguous 🙂
Better illustration
select surname as surname, firstname as firstname, middleName as firstname --note the renamed column in the result
from names
order by firstname
(although I haven't tried the above it ought to produce the same error)
February 10, 2008 at 3:27 pm
For whatever reason you need to display the same column twice, you should alias the column so try:
select distinct firstname as fname, firstname from customers
order by firstname
February 11, 2008 at 3:21 am
I don't see an error when I execute the following in Management Studio:
USE AdventureWorks
GO
SELECT DISTINCT FirstName, FirstName
FROM Person.Contact
ORDER BY FirstName
February 11, 2008 at 3:40 am
If the server you're connecting to is SQL 2000 or the adventureworks DB you're using is in compatability mode 80, the query will work fine.
Against a SQL 2005 server with the DB set to compatability mode 90, you will get an error. SQL 2005 is less forgiving on SQL syntax than 2000 was.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 11, 2008 at 3:48 am
If the server you're connecting to is SQL 2000 or the adventureworks DB you're using is in compatability mode 80, the query will work fine.
I see why its working now! But....
Against a SQL 2005 server with the DB set to compatability mode 90, you will get an error. SQL 2005 is less forgiving on SQL syntax than 2000 was.
I tried the same query in compatibility mode 90 and yes, it throws an error. I didn't quite get this though. Why does it work this way?
February 11, 2008 at 3:49 am
Gail, do you mean the syntax is incorrect in SS 2k5?
February 11, 2008 at 5:55 am
With DB set to compatibility level 90, which uid_design am I ordering by here..... because this works:
SELECT DISTINCT uid_design AS U1, uid_design AS U2
FROM Ad_Master_Import
ORDER BY uid_design
February 11, 2008 at 2:11 pm
Nisha (2/11/2008)
With DB set to compatibility level 90, which uid_design am I ordering by here..... because this works:
SELECT DISTINCT uid_design AS U1, uid_design AS U2
FROM Ad_Master_Import
ORDER BY uid_design
Neither/both. Without using the alias, or a name that appears in the select statement, the order by clause goes to the table directly for the sequence. (Basically, it grabs the same column three times, once for U1, once for U2, once for the Order By. That's not the whole story, but it's basically how it works.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 11, 2008 at 11:24 pm
Nisha (2/11/2008)
Gail, do you mean the syntax is incorrect in SS 2k5?
It's ambiguous. It isn't clear what you mean. Try this one
SELECT DISTINCT FirstName as Name, LastName as Name
FROM Person.Contact
ORDER BY Name
Which name should SQL order by?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 17, 2008 at 2:38 pm
Thanks for pointing out the difference in sql versions!
September 5, 2008 at 4:08 pm
Thanks a lot for the info.
This seems to make sense to an extent. But in my case this works fine on one SQL server running 2005 but not the other. In both the cases the DB and Stored procedure are restored from SQL Server 2000.
Can it be because on the first the DB is attached to run in mode 80 i.e. not migrated to a SQL Server 2005 DB and on the second its running in mode 90 i.e. migrated to a SQL Server 2005 DB from SQL Server 2000 DB?
September 5, 2008 at 8:47 pm
mj obi-wan kenobi (9/5/2008)
Thanks a lot for the info.This seems to make sense to an extent. But in my case this works fine on one SQL server running 2005 but not the other. In both the cases the DB and Stored procedure are restored from SQL Server 2000.
Can it be because on the first the DB is attached to run in mode 80 i.e. not migrated to a SQL Server 2005 DB and on the second its running in mode 90 i.e. migrated to a SQL Server 2005 DB from SQL Server 2000 DB?
Yes, I would say that is likely why.
Pretty strange behavior in 2k5... In the following the first 4 queries work fine. Just the 5th one fails. I never would have had a problem since I almost always use an alias on the table name and I would have put an alias on one of the fields just by habit.
USE AdventureWorks
GO
SELECT DISTINCT c.FirstName, c.FirstName
FROM Person.Contact c
ORDER BY c.FirstName
SELECT DISTINCT FirstName DISTINCT_FIRSTNAME, FirstName
FROM Person.Contact
ORDER BY FirstName
SELECT DISTINCT FirstName DISTINCT_FIRSTNAME, FirstName DISTINCT_FIRSTNAME
FROM Person.Contact
ORDER BY FirstName
SELECT DISTINCT FirstName, FirstName
FROM Person.Contact
ORDER BY 1
SELECT DISTINCT FirstName, FirstName
FROM Person.Contact
ORDER BY FirstName
Gary Johnson
Sr Database Engineer
September 6, 2008 at 3:48 am
Gary Johnson (9/5/2008)
mj obi-wan kenobi (9/5/2008)
Pretty strange behavior in 2k5...
Not really.
In query 1, because the expression in the order by is qualified with the table, it's clearly not a column alias. Hence SQL will use the column in the underlying table to do the order by and ignore the aliases (if any) declared in the select.
In query 2, there's only one column that's aliased Firstname and hence the order by runs on that.
In query 3, there's no column that's aliased Firstname and hence SQL has to go back to the table and use the column there
In query 4, you're explicitly saying order by the first column (positional), so there's no ambiguity over which one to order by.
It's only in query 5 that there are two meanings for ORDER BY Firstname. It could be equivalent to ORDER bY 1, or it could be equivalent to Order by 2. In this case, those two options will have the same effect, but that's not always the case (like in the silly example I posted above)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 6, 2008 at 4:09 am
mj obi-wan kenobi (9/5/2008)
Can it be because on the first the DB is attached to run in mode 80 i.e. not migrated to a SQL Server 2005 DB and on the second its running in mode 90 i.e. migrated to a SQL Server 2005 DB from SQL Server 2000 DB?
Just to clarify, even in compat mode 80, that is a SQL 2005 database. It's just behaving like SQL 2000. All the system tables are the 2005 ones and the database can no longer be attached to a SQL 2000 instance.
All the compat mode changes is how certain queries behave and whether or not the new features work.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply