records at max values

  • Thread starter Thread starter Jessica
  • Start date Start date
J

Jessica

Greetings!

I have a table set up that has a collection number, and
temperature, salinity, and pressure readings for each
second the equipment was deployed (see data below). What
I want to do is find the temperature and salinity values
for each collection number at each individual collection
number's maximum pressure. This value changes depending
on the collection number. How do I go about this? Can it
be done in one query or do I need several queries in order
to do this?

Thanks in advance!

Jessica

Example Dataset:

Coll Pres Temp. Sal.
030400 7.14 25.33 35.05
030400 5.24 25.40 35.07
030400 5.47 25.40 35.07
030400 5.69 25.40 35.07
030400 5.92 25.40 35.07
030400 6.16 25.38 35.08
030400 6.40 25.36 35.08
030400 8.82 25.21 35.05
030395 10.06 28.06 35.98
030395 10.40 28.05 35.97
030395 10.76 28.04 35.97
030395 11.11 28.03 35.97
030395 11.47 28.02 35.98
030395 11.82 28.01 35.99
030395 12.16 28.00 35.99
030395 12.51 27.99 35.99
030395 12.87 27.97 36.00
030395 13.25 27.93 36.01
030395 13.64 27.88 36.05
030395 14.02 27.80 36.14
 
Try something along the lines of
SELECT etc
FROM YourTable As T1
WHERE Pres IN (SELECT Max(Pres) FROM YourTable As T2 WHERE
T2.Coll = T1.Coll)

Hope This Helps
Gerald Stanley MCSD
 
Back
Top