CODEWITHSUNDEEP
sql
Nisha Nethani
Nisha Nethani

Reputation: 109

INNER JOINING THE TABLE ITSELF GIVES No column name was specified for column 2

    SELECT *
    FROM
    construction AS T2
    INNER JOIN  
    (
      SELECT   project,MAX(report_date)
       FROM construction 
            GROUP BY project
     ) AS R 

     ON T2.project=R.project AND T2.report_date=R.report_date

getting this error. plz help No column name was specified for column 2 of 'R'

Upvotes: 0

Views: 328

Answers (3)

Megha shah
Megha shah

Reputation: 232

You are getting this error because you have not specified column name for inner query

You have to write your query as

SELECT * FROM construction INNER JOIN
( SELECT project,MAX(report_date)"Max_ReportDate" FROM construction GROUP BY project ) Max_construction ON construction.project = Max_construction .project AND construction.report_date = Max_construction .Max_ReportDate

Upvotes: 0

amow
amow

Reputation: 2223

You should specific the MAX(report_date) with an alias report_date.
Because your table R have two columns project,MAX(report_date).

Upvotes: 0

Lukasz Szozda
Lukasz Szozda

Reputation: 175646

You need to add alias for MAX(report_date):

SELECT *
FROM construction AS T2
INNER JOIN  
(
  SELECT project,MAX(report_date) AS report_date
  FROM construction 
  GROUP BY project
) AS R 
  ON T2.project     = R.project 
 AND T2.report_date = R.report_date;

In SQL Server you can use syntax:

SELECT *
FROM construction AS T2
INNER JOIN  
(
  SELECT project,MAX(report_date)
  FROM construction 
  GROUP BY project
) AS R(project, report_date)
  ON T2.project     = R.project 
 AND T2.report_date = R.report_date;

Upvotes: 1

Related Questions