CODEWITHSUNDEEP
t-sqlsql-server-2008view
bitdiego
bitdiego

Reputation: 111

Creating a View from Table

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

enter image description here

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

Upvotes: -1

Views: 44

Answers (1)

uzi
uzi

Reputation: 4146

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'

Upvotes: 1

Related Questions