CODEWITHSUNDEEP
sql
user438199
user438199

Reputation: 63

Return only latest values from timestamped table

I have a table with the following structure:

timestamp, tagid, value

I want a query where I can receive only the newest timestamped values for a list of tag id's. For example:

SELECT * FROM floatTable WHERE tagid IN(1,2,3,4,5,6)

Will return the entire set of values. I just want the latest stored value for each one.

Upvotes: 2

Views: 94

Answers (2)

codingguy3000
codingguy3000

Reputation: 2835

How about this:

select vt.tagid, maxdate, t.value
from 
  ( 
    select tagid, max(timestamp) as maxdate
    from floatTable 
    group by tagid
  ) vt
inner join floatTable t on t.tagid = vt.tagid
where t.timestamp = vt.maxdate
and t.tagid in (1,2,3,4,5,6)

Upvotes: 1

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171401

select f.timestamp, f.tagid, f.value
from floatTable f
inner join (
    select tagid, max(timestamp) as timestampMax
    from floatTable
    where tagid in (1,2,3,4,5,6) 
    group by tagid
) fm on f.tagid = fm.tagid and f.timestamp = fm.timestampMax

Upvotes: 3

Related Questions