Problem pulling max measurement, including date, for each unique well in my table

Question

I have a very simple table that is structured like so:

Measurement_ID (primary key), Well_ID, Measurement, DateMeasured

The 'Measurement' column contains elevation values, and I'm looking for the max measurement value of each well.

Basically, what I'm trying to do is this:

SELECT Well_ID, MAX(Measurement), DateMeasured
FROM WellTable
GROUP BY Well_ID

Obviously, this doesn't work, because I'm not including DateMeasured in my GROUP BY. The result I'm looking for is a single row per well that includes the well ID, maximum measurement, and date of the max measurement. MAX(DateMeasured) in the SELECT does not work, and including DateMeasured in the GROUP BY also does not give me the result I expect.

Any feedback would be seriously appreciated. This feels like an incredibly simple problem, but the solution is unintuitive to me.

Does this answer your question? [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) — Charlieface, Apr 28 '21 at 21:51

score 1 · Accepted Answer · answered Apr 28 '21 at 21:05

You can use ROW_NUMBER() to identify which row you want.

For example:

select Well_ID, Measurement, DateMeasured
from (
  select *,
    row_number() over(partition by Well_ID
                      order by Measurement desc) as rn
  from WellTable
) x
where rn = 1

Problem pulling max measurement, including date, for each unique well in my table

1 Answers1