I have a table with a date and value , but how to i create a checker to give a value 1 to the most up to date record?

Original

Updated

CodePudding user response：

Using windowed MAX:

SELECT *, UpdatedDate = MAX(UpdatedDate) OVER(PARTITION BY ClientId) AS Outdated
FROM tab

CodePudding user response：

This is a select statement that will produce the output you're showing - this could be established as a view on top of your data.

If you're looking to maintain the "outdated" indicator as a persistent value in the DB, please advise:

select
    *,
    1 as outdated
from
    client_values
where
    (start_date, client_id, update_date) in (
        select
            start_date,
            client_id,
            max(update_date)
        from
            client_values
        group by
            start_date,
            client_id
    )
union
select
    *,
    0 as outdated
from
    client_values
where
    (start_date, client_id, update_date) NOT in (
        select
            start_date,
            client_id,
            max(update_date)
        from
            client_values
        group by
            start_date,
            client_id
    );