How to get min value at max date in sql?-CodePudding

I have a table with snapshot data. It has productid and date and quantity columns. I need to find min value in the max date. Let's say, we have product X: X had the last snapshot at Y date but it has two snapshots at Y with 9 and 8 quantity values. I need to get

product_id | date | quantity
     X        Y       8

So far I came up with this.

select 
  productid
  , max(snapshot_date) max_date
  , min(quantity) min_quantity
from snapshot_table
group by 1

It works but I don't know why. Why this does not bring min value for each date?

CodePudding user response：

I would use RANK here along with a scalar subquery:

WITH cte AS (
    SELECT *, RANK() OVER (ORDER BY quantity) rnk
    FROM snapshot_table
    WHERE snapshot_date = (SELECT MAX(snapshot_date) FROM snapshot_table)
)

SELECT productid, snapshot_date, quantity
FROM cte
WHERE rnk = 1;

Note that this solution caters to the possibility that two or more records happened to be tied for having the lower quantity among those most recent records.

Edit: We could simplify by doing away with the CTE and instead using the QUALIFY clause for the restriction on the RANK:

SELECT productid, snapshot_date, quantity
FROM snapshot_table
WHERE snapshot_date = (SELECT MAX(snapshot_date) FROM snapshot_table)
QUALIFY RANK() OVER (ORDER BY quantity) = 1;

CodePudding user response：

use row_number()

  with cte as (select *, 
  row_number() over(partition by product_id order by date desc) rn 
 from table_name) select * from cte where rn=1