I'm new to SQL, but I'm pretty sure something strange is happening here. I have a MySQL database with a table (eventtable) with 4 columns (RoomNum, EventName, EventStatus, EventDateTime).
Certain EventNames repeat with the EventStatus toggling ON and OFF at different times. I'm trying to get the latest entry so that I have the "current" state, so I use:
select RoomNum, EventName, EventStatus, max(EventDateTime) as MaxDate from eventtable where eventName = "DND" group by RoomNum
Just to make sure everything is ok, I check each room to make sure it is giving the latest one with:
SELECT * FROM `eventtable` where RoomNum = "070#" and eventname = "DND" /*where "#" is a specific digit*/ order by eventdatetime asc
The first one looked good:
But then the others don't! (The EventStatus is wrong even though the time is correct):
what I don't get is that EventDateTime matches which tells me I'm looking at the same row of data, but then how can the internal value (EventStatus) be different?
Please help. Thank you.