Find date of the change in Last version-CodePudding

I'm trying to show the date where the last version changed - for example in customer ACA.. the version changed in 12/5/2022

BTW, I have more than one customers.

CodePudding user response：

In your query you can use analytic functions ROW_NUMBER() and DENSE_RANK() and your SQL query it would like this:

SELECT date_, name, last_version FROM (SELECT date_, name, last_version,
           ROW_NUMBER() OVER (PARTITION BY last_version ORDER BY date_ ASC) as row_num,
           DENSE_RANK() OVER( ORDER BY last_version DESC) as d_rank
      FROM <your_table>) WHERE d_rank = 1 and row_num = 1

You can see here SQL query result:

db<>fiddle: https://dbfiddle.uk/?rdbms=oracle_18&fiddle=0fd33a31bdbd807926de8b2489e2883b

CodePudding user response：

You could do something like:

SELECT MIN(date)
    , name
    , last_version
FROM Datatable
GROUP BY name, last_version

CodePudding user response：

You can aggregate the dates to find the earliest per version, then the max of these is the date the version last changed:

select name, Max(last_version) last_version
from (
    select name, Min(date) last_version
    from t
    group by name, last_version
)t
group by name;