SQL query for selecting the firsts in a series by cloumn
- by SP
I'm having some trouble coming up with a query for what I am trying to do.
I've got a table we'll call 'Movements' with the following columns:
RecID(Key), Element(f-key), Time(datetime), Room(int)
The table is holding a history of Movements for the Elements. One record contains the element the record is for, the time of the recorded location, and the room it was in at that time.
What I would like are all records that indicate that an Element entered a room. That would mean the first (by time) entry for any element in a series of movements for that element in the same room.
The input is a room number and a time. IE, I would like all of the records indicating that any Element entered room X after time Y.
The closest I came was this
Select Element, min(Time)
from Movements
where Time > Y and Room = x
group by Element
This will only give me one room entry record per Element though (If the Element has entered the room X twice since time Y I'll only get the first one back) Any ideas? Let me know if I have not explained this clearly.
I'm using MS SQLServer 2005.
