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.

you wont be able to use ORDER BY because `SELECT TOP 1 FROM (` will turn it into a subquery, if i'm not mistaken — jcoke, Oct 16 '20 at 13:39
`SELECT TOP ` return the first n row. You want only the first? — vernou, Oct 16 '20 at 13:42

score 4 · Accepted Answer · answered Oct 16 '20 at 13:40

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.

Gosfly · Answer 2 · 2020-10-16T13:42:24.340

0

You are probably looking for something as simple as :

SELECT TOP 1 S.idindicator, S.val , 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

EDIT : As pointed in comment, limit doesn't exist in t-sql, use top 1 instead

edited Oct 16 '20 at 13:42

answered Oct 16 '20 at 13:39

Gosfly

1,240
1
8
14

2

`LIMIT` isn't a T-SQL operator; it uses `TOP`. – Thom A Oct 16 '20 at 13:40

How do you select the value for the latest date?

2 Answers2