Possible to do in SQL? - Solved. Thanks WannaFly

[aceO07]

I have a table for various objects and their events along with datetime. The table tracks the objects and all the events that go through like an event log. Each time something happens to the object, a new record is created.

How would I query the table to find ALL the objects with a certain last status.

for example, a room can be painted, carpeted, cleaned.. etc and each event can be done any number of times in any order. I want to find all the rooms which were painted as their last status/event.

 

[WannaFly]

There might be a better way but this seems to work

SELECT room,date FROM dbo.test T1
WHERE status='painted'
AND date=(SELECT MAX(date) FROM test T2 WHERE T1.room=T2.room)

 

[aceO07]

Thanks WannaFly. That did it. I've gotten lazier in the last couple of years and just do simple queries and have code handle the rest.

I should really review my sql textbook again. Probably cost me > $100 before.

 

[aceO07]

That did answer my question. Unfortunately I forgot that I also needed all the records for each of those rooms too. So, for all the rooms that were last painted, what were all the events that happened prior. That might need another query.

:edit - Nevermind. There's another routine that does query for the log formating.

 

[KentState]

Just use WannaFly's query as a subquery against the same table.