SQL Group by but additional column info is needed-CodePudding

I'm stuck with this query. I wonder if somebody can give me some idea how to resolve this.

Here is my table:

enter image description here

I basically want to group by product with the highest value of quality. But at the same time I also need to grab completed column.

select 
    Product, max(Quality) as Quality 
from 
    [Table]
group by 
    Product

When I group it, I cannot retrieve completed column.

enter image description here

Any other method to have same result above with completed column? in this case 1, 1 will be displayed.

Thanks in advance

CodePudding user response：

You can take the output of this query and inner join it with the original table...

select t1.*
from table_name t1
inner join 
    (select product, max(quality) as maxquality
     from table_name
     group by product) t2 on t1.product = t2.product 
                          and t1.quality = t2.maxquality

CodePudding user response：

In SQL Server you can only select columns that are part of the GROUP BY clause, or aggregate functions on any of the other columns.

You can either try adding it to the group by clause

select Product, 
       Completed,
       max(Quality) as Quality,
from [Table]
group by Product, Completed

or use an aggregate function

select Product, 
       max(Completed),
       max(Quality) as Quality,
from [Table]
group by Product