Select multiple columns with not all columns mentioned in Groupby – Postgres v12

I have a table which contain review_id,product_id,ratings,reviewer_id,review_comments. The table i have is as below.

My need is quite simple but I have issues figuring it out. Need is to get product_id, rating, reviewer_id and review_comments of the product_id which has the max value of review_id

With below query, I am able to get product_id and review_id properly.

SELECT product_id,max(review_id) as review_id
    FROM public.products Group by product_id;

But when I try to add ratings, reviewer_id, and review_comments, it raises an error that those columns have to be part of a groupby and if I add those columns, grouping gets disturbed since I need grouping only on product_id and nothing else.

Is there a way to solve this?

My expected result should contain all row content with review_id 7,5,8 since for product_id 1 review_id 7 is highest and for product_id 2 review_id 5 is highest and for product_id 3 review_id 8 is highest.

Answer

You can try below way-

select * from tablename a
where review_id =(select max(review_id) from tablename b where a.product_id=b.product_id)

or use row_number()

select * from
(
select *, row_number() over(partition by product_id order by review_id desc) as rn
from tablename
)A where rn=1

Advertisement

Answer