Identifying the last time a value was used in a column based upon dates in another column-CodePudding

I'm presently looking at a data set that records each time a note is placed on a client's record. Each note can be given a specific type, and I'm trying to identify how to identify the last time each note type was used.

The table is like the one below:

PERSON_ID	NOTE_TYPE	DATE_CREATED
111111	NOTE1	02/01/2022
121654	NOTE12	03/04/2015
115135	NOTE1	25/06/2020

The Person ID is irrelevant - what I need is data like the one below, only showing each note type once, and the date it was last used:

NOTE_TYPE	DATE_CREATED
NOTE1	02/01/2022
NOTE12	03/04/2015

I'm relatively new to SQL and have tried some very basic code, including adapting an example I found online:

SELECT NOTE_TYPE, 
       DATE_CREATED
from ( SELECT NOTE_TYPE, 
              DATE_CREATED, 
              ROW_NUMBER() over (partition by NOTE_TYPE order by DATE_CREATED) as rn
      from CASE_NOTES
) t
where rn = 1
ORDER BY DATE_CREATED

I know this doesn't work because one note type that was used yesterday came back with it having last been used in 2004!

CodePudding user response：

You are describing a simple aggregation:

Select Note_Type, max(Date_Created) Date_Created
from t
group by Note_Type;

CodePudding user response：

SELECT
   NOTE_TYPE, 
   DATE_CREATED
from
(
   SELECT
      NOTE_TYPE, 
      DATE_CREATED, 
      ROW_NUMBER() over (partition by NOTE_TYPE order by DATE_CREATED DESC) as rn
   from CASE_NOTES
) t
where rn = 1
ORDER BY DATE_CREATED