SQL Server – Select Most Recent Row by Another Column

distinctgroup bysql serversql-server-2008-r2

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.

enter image description here

enter image description here

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.

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

Related Question