Q:

Need Strategy To Generate Snapshot Of Table Contents After a Series of Random Inserts

There are 10 rooms with a set of inventory items.  When an item is added/deleted from a room a new row gets inserted to a MS-SQL table.  I need the latest update for each room. 
Take this series of inserts:
 id| room| descriptor1| descriptor2| descriptor3|
 1 | A   | blue       | 2          | large      |
 2 | B   | red        | 1          | small      |
 3 | A   | blue       | 1          | large      |

What the resulting table needs to show:
room| descriptor1| descriptor2| descriptor3|
A   | blue       |1           | large      |
B   | red        |1           | small      |

Ideally, I would write a trigger that would update a room status table.  I could then just query the room status table (Select *) to obtain the result.  However, this table does not belong to me, I only have read access to a constantly updated table.  I need to poll periodically or when I need a report. 
How do I do this in MS-SQL?  I have some inkling of how I would do it to obtain the status of just one room something like:
SELECT descriptor1, descriptor2, descriptor3
FROM myTable mt1
WHERE id = (SELECT MAX(id)
               from myTable mt2
               WHERE room = 'A'    
              );

Since I have 10 rooms i would need to do this query 10 times.  Can this be narrowed down to a single query?  What happens when there are 100 rooms?  Is there a better way? 
Thanks! 
Matt

A:

You were very close:
SELECT descriptor1, descriptor2, descriptor3
FROM myTable mt1
WHERE id in (SELECT MAX(id)
               From myTable
               Group By room
              );

Instead of creating a trigger to update a static table, you should look into creating a view.