Select most recent row by some other column

Question

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
...

Answer 1

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

Answer 2

An example using the Row_Number() 'windowing' function.

Sample data:

DECLARE @CRV_Rooms TABLE (
    RoomID INT
    ,RoomName VARCHAR(50)
    ) ;
DECLARE @CRV_AttributeLog TABLE (
    AttributeID VARCHAR(30)
    ,AnalogValue INT
    ,RoomID INT
    ,LogTimeStamp DATETIME
    ) ;

insert into @CRV_Rooms 
    (RoomID, RoomName) 
values 
    (1,'Room1'), 
    (2,'Room2') ;

insert into @CRV_AttributeLog 
    (AttributeID, AnalogValue , RoomID , LogTimeStamp)
values
    ('online_status',1,1,'2017-01-01'),
    ('online_status',1,1,'2017-02-01'),
    ('online_status',1,1,'2017-03-01'),
    ('online_status',2,2,'2017-01-01'),
    ('online_status',2,2,'2017-06-01') ;

The query:

;
WITH Rooms
AS (
    SELECT r.RoomName
        ,a.AttributeID
        ,a.AnalogValue
        ,row_number() OVER (
            PARTITION BY r.Roomid ORDER BY a.LogTimeStamp DESC
            ) AS rn
        ,a.LogTimeStamp
    FROM @CRV_AttributeLog a
    JOIN @CRV_Rooms r ON a.RoomID = r.RoomID
    WHERE a.AttributeID LIKE 'online_status'
    )
SELECT *
FROM Rooms
WHERE rn = 1 ;

Basically, the above example Groups By RoomID and ORDERS BY LogTimeStamp in a descending order. The Row_Number OVER windowing function assigns a row number to each row and starts the count over (partition by) when a new RoomId is encountered. So, now you have a 'group' of rows for each room and they are ordered by LogTimeStamp descending. The latest row for each room will have a row number of 1 (see the WHERE clause at the very bottom) and that is how you isolate the latest row for each room.