Mysql query with max prices-CodePudding

I am using MySql. I have two tables. One for products and other to prices. I need list all products with your latest prices.

Products:

id name  cat
1  prod1  a
2  prod2  b
3  prod3  c

Prices:

id prod_id cod   price  
1  1       cod1  10.00 
2  3       cod2  20.00
3  1       cod3  5.00
4  3       cod4  12.00

I´d like a result with all products and the corresponding price and cod considering the max id with list prices.

For example:

I need a result as:

id name   cat  cod     price
1  prod1  a    cod3    5.00
2  prod2  b    null    null
3  prod3  c    cod4    12.00

I have tried something as below, but I don´t think to be a good solution:

select  A.id,A.name, B.cat,B.cod,B.price      
      from products A
      left join prices B ON B.prod_id = A.id 
          AND B.id = 
            (
              SELECT MAX(B.id) 
              FROM prices Z 
              WHERE Z.prod_id = A.id
            )

CodePudding user response：

You can use row_number to get the max price on Prices table and then join with the product table:

with cte as (
           select *,
                  row_number() over(partition by prod_id order by price  desc) row_num
           from Prices        
 )  select pd.id,pd.name,pd.cat,cte.cod,cte.price
    from Products pd
    inner join cte on cte.prod_id =pd.id
    where cte.row_num=1;

Above query returns the max price which is not the expected result on the question.If you want to take the max id change order by price desc to order by id desc