Thanks very much, Tom. While the DISTINCT ON suggestion answered the
question I asked very neatly and I am glad to add that concept to my
arsenal, your standard-compliant query was what I actually needed. The
DISTINCT ON query only gave me one coil if there were two coils in a
charge that had the same coldspot time. Your standard-compliant query
included them. There is a fourth column of interest, named coil_trf.
The purpose of this exercise is to map coil_trf values to coldspot times
for the coils in each charge that have the largest coldspot time. If
two coils have the same coldspot time and none of the others in the
charge have one as long, then I want both coils.
The key concept here that I didn't know about was the use of more than
one field in a list used with IN.
Using actual field names from the database (except for coil_trf, which I
haven't added yet because it comes from another table), here's the query
I ended up with:
select coil_id, inventory.charge, heating_coldspot_time_reached
from inventory
inner join charge on charge.charge = inventory.charge
where base_type = '3' and heating_coldspot_time_reached > 0 and
inventory.status = 'Done' and inventory.charge >= 1000 and
(inventory.charge, heating_coldspot_time_reached) in
(select inventory.charge, max(heating_coldspot_time_reached)
from inventory
inner join charge on charge.charge = inventory.charge
where base_type = '3' and heating_coldspot_time_reached > 0 and
inventory.status = 'Done' and inventory.charge >= 1000
group by inventory.charge)
order by inventory.charge
RobR
