I'm trying to find a way to select the minimum or maximum from a range of data based on these conditions:
Sample table: sold_out
location_cd | machine_cd | product_cd | temperature | data_source | created_timestamp |
---|---|---|---|---|---|
0012345678 | 1234567890 | 123456 | 1 | 3 | 2024/06/10 15:30:15 |
0012345678 | 1234567890 | 123456 | 1 | 4 | 2024/06/10 15:30:15 |
0012345678 | 1234567890 | 654321 | 0 | 3 | 2024/06/10 14:50:15 |
0012345678 | 1234567890 | 654321 | 0 | 4 | 2024/06/10 15:00:15 |
Expected output :
location_cd | machine_cd | product_cd | temperature | data_source | created_timestamp |
---|---|---|---|---|---|
0012345678 | 1234567890 | 123456 | 1 | 3 | 2024/06/10 15:30:15 |
0012345678 | 1234567890 | 654321 | 0 | 4 | 2024/06/10 15:00:15 |
I tried this, but it is not working.
SELECT machine_cd,
location_cd,
product_cd,
temperature,
case WHEN exists(
SELECT machine_cd,
location_cd,
product_cd,
temperature,
created_timestamp,
COUNT(*)
From sold_out
group by machine_cd,
location_cd,
product_cd,
temperature,
created_timestamp
HAVING COUNT(*) > 1
) THEN MIN(data_source) ELSE MAX(created_timestamp) END
FROM sold_out
GROUP BY machine_cd,
location_cd,
product_cd,
temperature
Like all TOP 1 per group
problems you can just create a row number partitioned and ordered as per your requirements and use that to filter the rows you need e.g.
with cte as (
select *
, row_number() over (partition by location_cd, machine_cd, product_cd, temperature order by created_timestamp desc, data_source asc) rn
from TestData
)
select location_cd, machine_cd, product_cd, temperature, data_source, created_timestamp
from cte
where rn = 1
order by location_cd, machine_cd, product_cd, temperature, data_source, created_timestamp;