Creating a View from Table

Question

i am trying to create a view from a SQL table following is a (very simplified...) subset of data in the table:

in the view i have to show only the EC_MODEL that have Installation as last EC_TYPE that is, from the table, only the sensor from Station1 should be selected; on the contrary, the EC_MODEL from Station2 must not be selected, because the last operation is a Removal. Well, I couldn't write the query that does the operation...how could I solve ? thanks

Answer 1

Try this query:

select
    ec_model
from (
    select
        ec_model, ec_type, rn = row_number() over (partition by ec_model order by operation_date desc)
    from
        myTable
) t
where
    rn = 1
    and ec_type = 'installation'