I have some data like this:

I need the avgearge price and latest price for each product in one table so it should look like this:

To sum up, I just need to combine these to queries:

SELECT  Price  FROM MyTable
where Date = (select max(Date) FROM MyTable)
group by Product

and

SELECT  Avg(Price) FROM MyTable
group by Product

How can I write this to work properly?

CodePudding user response：

You may use the following which uses row_number to help determine the latest price in a subquery.

SELECT
    Product,
    AVG(Price) as avg_price,
    MAX(
        CASE WHEN rn=1 THEN Price END
    ) as latest_price
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY Product ORDER BY Date DESC) rn
    FROM
        my_table
) t
GROUP BY Product;