[Last Call] Learn how to a build a cloud-first strategyRegister Now


SQL Select within select

Posted on 2011-10-20
Medium Priority
Last Modified: 2013-12-16

select * from  --this is an example the star will be replaced with desired columns

--these next two selects work perfectly, they return the desired rows
--I would like to use their results in the outer select
--However, I get this error
--Msg 8156, Level 16, State 1, Line 18
--The column 'machine_id' was specified multiple times for 'finalResult'.
--Thank you for your input.

select * from [UpdateHistory] as i
inner join
(select u.machine_id, u.application_id, max(servertimestamp) as [timestamp]
from [UpdateHistory] u      
group by machine_id, application_id) p
i.machine_id = p.machine_id AND
i.application_id = p.application_id AND
i.servertimestamp = p.[timestamp]

) as finalResult
Question by:wilfordrocks
1 Comment
LVL 23

Accepted Solution

Rajkumar Gs earned 2000 total points
ID: 37000149
If your sub-query is returning more than one column with the same name, this error could occur

eg: - In 'users' table, i have a column 'user_id. * means it fetches all records, right - which includes user_id as well. In addition to that I have mentioned user_id again.
select * from
select *, user_id from users
) a

Open in new window


Featured Post

Simplify Active Directory Administration

Administration of Active Directory does not have to be hard.  Too often what should be a simple task is made more difficult than it needs to be.The solution?  Hyena from SystemTools Software.  With ease-of-use as well as powerful importing and bulk updating capabilities.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to install and use the NTBackup utility that comes with Windows Server.
I was prompted to write this article after the recent World-Wide Ransomware outbreak. For years now, System Administrators around the world have used the excuse of "Waiting a Bit" before applying Security Patch Updates. This type of reasoning to me …
This tutorial will walk an individual through locating and launching the BEUtility application to properly change the service account username and\or password in situation where it may be necessary or where the password has been inadvertently change…
This tutorial will walk an individual through configuring a drive on a Windows Server 2008 to perform shadow copies in order to quickly recover deleted files and folders. Click on Start and then select Computer to view the available drives on the se…
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question