Search code examples
sqlfiltergroup

SQL Group by but additional column info is needed

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

Solution

  • 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