pervasive-sql

select min and max value of a column and corresponding date (another column)

I am trying to get the lowest cost for a part and the highest cost for a part. along with the dates that they occurred. I almost have what I want except that in my query I don't know how to associate the lowest cost with the correct date. I understand why (because that's what I told the query to do) but I am wondering how can I alter the query so that when the lowest cost is selected, it selects the corresponding date as well?

Data set example:

|Part  |    cost   |date_received|
|------|-----------|-------------|
|846060| 28.373265 |   1/5/2022  |
|846060| 29.143835 |   2/28/2022 |
|846060| 27.588483 |   3/8/2022  |
|846060| 29.143835 |   4/25/2022 |

Desired output:

|Part  |lowest_cost|date_received|highest_cost|last_date_received|difference|
|------|-----------|-------------|------------|------------------|----------|
|846060| 27.588483 |  3/8/2022   | 29.143835  |  4/25/2022       | 1.555405 |

current output:

|Part  |lowest_cost|date_received|highest_cost|last_date_received|difference|
|------|-----------|-------------|------------|------------------|----------|
|846060| 27.588483 |  1/5/2022   | 29.143835  |  4/25/2022       | 1.555405 |

query I have currently:

select part,min(cost) as Lowest_Cost,max(cost) as Highest_Cost, 
  min(date_received) as First_date, 
  max(date_received) as Last_Date, (max(cost) - min(cost)) as Difference
from v_po_history 
where part not like '*%' and date_received >= '2022-01-01' and date_received <= '2022-05-01' and location = 'HS' and part = '846060'
group by part
Solution 
  • select g.part, min_cost, (select min(p1.date_received) from v_po_history p1 where p1.part = g.part and p1.cost = g.min_cost and part not like '*%' and DATE_RECEIVED >= '2022-01-01' and date_received <= '2022-05-01' and location = 'HS') as min_cost_date, 
max_cost, (select max(p2.date_received) from v_po_history p2 where p2.part = g.part and p2.cost = g.max_cost and part not like '*%' and DATE_RECEIVED >= '2022-01-01' and date_received <= '2022-05-01' and location = 'HS') as max_cost_date 
from (select part, min(cost) min_cost, max(cost) max_cost from v_po_history 
    where part not like '*%' and DATE_RECEIVED >= '2022-01-01' and date_received <= '2022-05-01' and location = 'HS'
    group by part) g