How do you select the value for the latest date?

Question

I'm trying to simply select the value which has the latest timestamp, but for some reason I'm having some sort of brain freeze.

The code below is selecting all the records:

SELECT S.idindicator, S.val ,[Time] = MAX(CAST(S.valTimestamp as date)), S.valTimestamp
FROM Status as S
WHERE S.INVALID = 0 
    AND S.id = 16888
GROUP by S.idindicator, S.val, S.valTimestamp 
ORDER BY S.valTimestamp DESC

How do I simply just select the val which has the latest date, which is 75.00?

Note: I have done it using a correlated subquery, however, it turns it into an expensive query.

Answer 1

If you want just one row, use top (1) and order by:

select top (1) *
from status s
where invalid = 0 and id = 16888
order by valTimestamp desc

If you want the same result over multiple ids, then one option uses window functions:

select *
from (
    select s.*, row_number() over(partition by id order by valTimestamp desc) rn
    from status s
    where invalid = 0
) s
where rn = 1

If you want to allow ties, then you would use top (1) with ties in the first query, and rank() instead of row_number() in the second query.