I have a table with this structure:

STATION ID	YEAR	MONTH	DAY	RECDATE	VALUE
123456	1950	01	01	01-01-1950	95
123456	1950	01	15	01-15-1950	85
123456	1950	03	15	03-15-1950	95
123456	1951	01	02	01-02-1951	35
123456	1951	01	10	01-10-1951	35
123456	1952	02	12	02-12-1952	80
123456	1952	02	13	02-13-1952	80

And so on. There's a TMIN value for this station ID for every day of every year between 1888 and 2022. What I'm trying to figure out is a query that will give me the earliest date in each year that a value between -100 and 100 occurs.

The query

select year, max(value) from table where value between -100 and 100 group by year order by year

gives the year and value. The query

select recdate, min(value) from table group by recdate order by recdate

gives me every recdate with the value.

I have a vague memory of a query that practically partitions the data by a year or a date range so that the query would look at all the 1950 dates and give the earliest date for the value, then all the 1951 dates, and so on. Does anyone remember queries like that?

Thanks for any and all suggestions.

CodePudding user response：

If I understood you correctly, this is your question:

What I'm trying to figure out is a query that will give me the earliest date in each year that a value between -100 and 100 occurs.

Then you posted 2 queries which return something, but I don't see relation to the question. What was their purpose? To me, they look like some random queries one could write against data in that table.

Therefore, back to the question: isn't that just

select min(recdate),                 --> "earliest date
       year                          -->  in each year
from that_table                      --   that a   
where value between -100 and 100     -->  value between -100 and 100 occurs"
group by year