How to get min value at max date in sql?

February 22, 2022

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?

>Solution :

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.