3

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
...
  • Sample data, with edge cases, and desired results for that sample data? Including how to break ties if two rows have the same latest timestamp? – Aaron Bertrand Feb 20 '17 at 18:02
  • But is it LIKE 'Online status' or = 'Online status'? – McNets Feb 20 '17 at 18:35
  • As far as I can tell, the data is only "ONLINE_STATUS", but the case may change without any notice. I'm working with a 3rd party product, and they are known to change case randomly as versions (and other variables) change. – YetAnotherRandomUser Feb 20 '17 at 18:39
  • If the value changes to something else, LIKE won't capture that - the LIKE you've written is effectively = so you should probably write = (this won't change performance or mechanics, but it makes intent much more clear). – Aaron Bertrand Feb 20 '17 at 19:43
5

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

  • AttributeID is a human-readable key describing the attribute, for example ONLINE_STATUS. There is more than 1 possible value, and I am only interested in one particular value: ONLINE STATUS – YetAnotherRandomUser Feb 20 '17 at 18:23
  • Then first query should works – McNets Feb 20 '17 at 18:24
  • Different attributes and different analog values for the same room? – McNets Feb 20 '17 at 18:25
  • See update 3; I put it in the OP rather than a comment. – YetAnotherRandomUser Feb 20 '17 at 18:35
  • I'll give a try to the second one. – McNets Feb 20 '17 at 18:45
3

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.

  • That's the sort of thing that looks horribly overcomplicated to me. FOr example, why are there dates hardcoded in there? This query will form the basis of a report later on, so it has to be entirely referential. – YetAnotherRandomUser Feb 20 '17 at 18:26
  • 1
    The dates were sample data to make sure the query was working correctly. My query would start at WITH Rooms. By including sample data in my answer, I'm able to provide what's known as a 'Minimal, Complete, and Verifiable example' – Scott Hodgin Feb 20 '17 at 18:26
  • 5
    @Dan there are no dates hard-coded. This is a better way to present sample data than screen shots of grid results (which we can't copy and paste). – Aaron Bertrand Feb 20 '17 at 18:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.