tSQL query- name of column question.

sometimes,
select column_name,* from tablename
brings back results fine.

other times, it seems to say 'you can't have duplicate column names'.

what causes this?
LVL 5
25112Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
> select column_name from tablename
This returns column column_name

>select * from tablename
This returns all columns from table_name, including column_name, so now column_name is returned twice.

Since there's no SQL construct to the effect of 'SELECT all column names EXCEPT FOR column_name', then to pull this off you'll have to replace * with all column names you wish except for column_name.
0
Deepak ChauhanSQL Server DBACommented:
you can test it like this.

1. Select name, * from sys.databases  ---- ( No error will execute fine sql knows the table name here)

2. select name, * from sys.databases a
     join sys.databases d
    on a.name=d.name

----( Now error  "Ambiguous column name 'name'.")
Because sql can not decide from which table it has to display the "Name" column.)

3. select D.name, a.* from sys.databases a
join sys.databases d
on a.name=d.name

---(no error)
0
25112Author Commented:
what you said helped.

this is the query that threw an error:

select top 100 backup_start_date ,type,* from msdb..backupset where name = 'THAGAPANE' and database_name = 'VARUMYESUVAE'
and type = 'l'
order by backup_start_date desc

(if you avoid the order, there is no problem)
0
Deepak ChauhanSQL Server DBACommented:
Two column in the list on which sql has to perform sort operation. In this case you have to mention the ordinal position of column like Order by 1 or 2....3
or
you have to mentioned table alias " From msdb..backupset b order by b.backup_start_date"
or
from msdb..backupset.backup_start_date

select top 100 backup_start_date ,type,* from msdb..backupset where name = 'THAGAPANE' and database_name = 'VARUMYESUVAE'
and type = 'l'
 order by msdb..backupset.backup_start_date desc ---(or order by 1 desc)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
25112Author Commented:
thank you
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.