Select most recent row by some other column

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

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.

WITH maxTime as
(
    SELECT   RoomId, MAX(LogTimeStamp) AS LogTimeStamp
    FROM     @CRV_AttributeLog
    WHERE    AttributeID LIKE N'online_status'
    GROUP BY RoomId
)
SELECT  r.RoomName, 
        a.AttributeId, 
        a.AnalogValue,
        m.LogTimeStamp
  FROM  @CRV_AttributeLog a 
  JOIN  maxTime m 
    ON  a.RoomID = m.RoomID
   AND  a.LogTimeStamp = m.LogTimeStamp
   AND  a.AttributeId LIKE N'online_status'
  JOIN  @CRV_Rooms r
    ON  r.RoomId = a.RoomId; 

+-----------------+---------------+-------------+---------------------+
|     RoomName    |  AttributeId  | AnalogValue | LogTimeStamp        |
+-----------------+---------------+-------------+---------------------+
| Great Hall      | online_status |      1      | 01.01.2017 20:21:00 |
| Boardroom       | online_status |      0      | 01.01.2017 21:40:00 |
| Conference room | online_status |      1      | 01.01.2017 20:20:00 |
| Gymnasium       | online_status |      2      | 01.01.2017 18:20:00 |
+-----------------+---------------+-------------+---------------------+

Check it here: http://rextester.com/HISDOM70855

StackExchange Database Administrators Q#164918, answer score: 5