SQL query for selecting the firsts in a series by column

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 r开发者_如何学编程oom 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.

This sounds like a typical min per group and in SQL Server can be solved by using ROW_NUMBER:

SELECT Element, Time
FROM (
    SELECT
        Element, Time, 
        ROW_NUMBER() OVER(PARTITION BY Element ORDER BY Time) AS rn
    FROM Movements
    WHERE Room = 1 AND Time > '2010-06-01 00:30:00'
) T1
WHERE rn = 1

Example result:

Element  Time                   
1        2010-06-01 02:00:00.000
2        2010-06-01 03:00:00.000

Test data:

CREATE TABLE Movements (RecID INT NOT NULL, Element INT NOT NULL, Time DATETIME NOT NULL, Room INT NOT NULL);
INSERT INTO Movements (RecID, Element, Time, Room) VALUES
(1, 1, '2010-06-01 00:00:00', 1),
(2, 1, '2010-06-01 01:00:00', 2),
(3, 1, '2010-06-01 02:00:00', 1),
(4, 2, '2010-06-01 03:00:00', 1),
(5, 2, '2010-06-01 04:00:00', 2),
(6, 1, '2010-06-01 05:00:00', 3),
(7, 2, '2010-06-01 06:00:00', 2);