I'd like to select the most recent row in a MS SQL Server 2008 R2 database based on a couple of factors. I've seen a lot of people want to do the same thing online, and a lot of different proposed solutions, but most seem overcomplicated, and I can't get anything to work properly or do what I want.
I have data in a table where a particular thing (Room) will have a name, and a most recent status (AnalogValue; it correlates to online status) with a corresponding date/time (LogTimeStanp).
I'm most interested in the current online status for each room, so I just need the most recent row for each room.
- There can be a number of rooms, for an example 1 – 5,000 (there's no imposed upper limit).
- There is a lot of data that pulls back when I look at the historic online
status. - There is other data in this table that I'm not interested in for this query.
- I have to join tables to get a readable room name, otherwise I just have a GUID.
- I'm not sure if I need to use
MAX()
,SELECT DISTINCT
,Group by
, or something else. I have not been able to get these to work to my satisfaction.
This code does not work, but should give you an idea of what I want to do:
SELECT r.RoomName, a.AttributeID, a.AnalogValue, max(a.LogTimeStamp)
FROM CRV_AttributeLog a join CRV_Rooms r on a.RoomID=r.RoomID
where a.AttributeID like 'online_status'
My intent is to get the AnalogValue with the max (most recent) LogTimeStamp per individual room.
I have gotten a query to run with group by, but it doesn't seem to be grouping it (and certainly not by room name), so I guess I am doing it wrong.
Update 1, 2, 3, 4
Added pics for context. Also, this query will form the basis of a report, so it has to be entirely referential, that's why I just want "the latest" online status. Further complicating things, "the latest" may not have happened today, this week, or this month. But it's in there, somewhere, sometime.
Each row in CRV_AttributeLog will have its own AttributeID and AnalogValue, and the row is made unique by the time stamp (LogTimeStamp). Otherwise, it's entirely possible for AttributeID and AnalogValue to be matching across rows (and that's OK). This table is logging information about when a few different somethings change: online status, if there is an error, etc. ONLINE_STATUS
can change from 0-2 (Offline, partially online, online) and will change between them periodically. The other AttributeIDs' AnalogValues may also change back and forth depending on other conditions.
Desired Output
Room Name AttributeID AnalogValue LogTimeStamp
Conference Room ONLINE_STATUS 0 2016-06-11 21:21:25:123
Gymnasium ONLINE_STATUS 1 2016-07-21 20:23:45:456
Boardroom ONLINE_STATUS 2 2017-02-17 05:15:37:951
Great Hall ONLINE_STATUS 0 2016-10-23 07:28:54:753
...
Best Answer
If there are more than one AnalogValue, you can get max value for each RoomId and then JOIN with CRV_AttributeLog just to fetch all attributes.
Check it here: http://rextester.com/HISDOM70855