SQL query for selecting the firsts in a series by column

Question

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.

Answer 1

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);