Finding the lowest price for each category using j

Question

I have to write Hive SQL.

I want to find the lowest price for each category. I want to get one row for each category with the category ID, the product ID, the price (lowest price), and the image url columns. In the example below, we finally need two rows.

Help me. Thank you.

"category_product_match" table

category_id    product_id
1001             500001
1001             500002
1002             500101
1002             500102
1002             500103

"product_info" table

prd_id      price      img_url
500001        29000      /app/url/img/500001.jpg
500002        29500      /app/url/img/500002.jpg
500101        8100       /app/url/img/500101.jpg
500102        8100       /app/url/img/500102.jpg
500103        8500       /app/url/img/500103.jpg

Answer 1

You can use window functions:

select pc.*
from (select pi.*, cpm.category_id,
             row_number() over (partition by cpm.category_id order by pi.price asc) as seqnum
      from product_info pi join
           category_product_match cpm
           on cpm.product_id = pi.prd_id
     ) pc
where seqnum = 1;

Answer 2

use Min aggregate function with inner join and group by with category id

 Select category_id ,product_id, Min(price) as lowest_Price,img_url
 from  category_product_match
 inner join product_info on product_id=prd_id
 group by category_id;  

Demo here Sqlfiddle