I have a SQL table with a following structure:
CREATE TABLE dataLog (
tstamp datetime NOT NULL,
identifier smallint NOT NULL,
payload binary(46) NOT NULL,
PRIMARY KEY (tstamp, identifier));
In this table logging infrastructure logs data for many devices. The device identifier ranges from eg. 1 to 125. That mean column "identifier" has values from 1-125. The payload column holds binary data from each device (logging information, temperatures etc.) The tstamp column holds current time information.
How can one build a query to get a 'snapshot' information for each device in a given time point. E.g. I want to know what was the payload column value for each identifier (125) in eg. 2012-06-12 12:00:00. The data in the table were written when the device sends it and that's why the data are not with the exact timestamp given above but eg. 2 devices sent data on 2012-06-12 11:59:59, 10 devices on 2012-06-12 11:15:30 etc. The data should be detected backwards in time.
The expected result: 125 rows with each identifier, timestamp of each measurement and payload value for each identifier.
The data is needed to draw eg. a plot of the temperature across all of the devices from 1 to 125 at a given timestamp and next iterate through the data in eg. 5 minutes steps.
By "snapshot", I assume you mean the most recent record for each identifier before the time. Here is a way using ranking functions:
select dl.*
from (select dl.*,
row_number() over (partition by identifier order by tstamp desc) as seqnum
from DataLog dl
where tstamp <= @YOURTIMESTAMPHERE
) dl
where seqnum = 1