subquery has too may columns-CodePudding

I would like to get the following return from the example table below.

Example table name "project"

project | date | price
abc | 2020-01-07 | 50
abc | 2020-02-14 | 14
abc | 2020-03-20 | 84
ddd | 2020-01-08 | 30
ddd | 2020-02-10 | 45
ddd | 2020-04-10 | 50

expected return

project|year|quarter|**date**|price
abc | 2020|1|2020-03-20|84
ddd | 2020|1|2020-02-10|45
ddd | 2020|2|2020-04-10|50

query

select 
 project, 
 date_part('year', project.date),
 date_part('quarter',project.date),
 date
 max(price)     
from project 
where date in ( select
 date_part('year', project.date),
 date_part('quarter',project.date)
 from project
)
group by 1,2,3

If I use the query above, it returns "subquery has too many columns" and an error direct to where clause.

How can I fix this problem? Thank you in advance.

CodePudding user response：

SELECT  bb.project ,    bb.year 'year', bb.quorter 'quorter',   max(bb.date ) 'date',   max(bb.price ) 'price'  
FROM (SELECT    aa.project ,  year([date]) 'year',DATEPART(QUARTER,[date]) 'quorter',
      [date]   'date' ,   aa.price   'price'    FROM  project  aa) bb 
group by bb.project,bb.year ,   bb.quorter

i use sql server

CodePudding user response：

Remove the subquery altogether:

select
  project,
  date_part('year', project.date) as year,
  date_part('quarter', project.date) as quarter,
max(price)
from project
group by 1, 2, 3

Consider renaming the project column of the project table to name or code to increase clarity and decrease confusion.