I seldomly use SQL and I cannot find anything similar in my archive so I'm asking this simple query question: I need a query which one returns personID and only the first seenTime
Records:
seenID | personID | seenTime
108 3 13:34
109 2 13:56
110 3 14:22
111 3 14:31
112 4 15:04
113 2 15:52
Wanted result:
personID | seenTime
3 13:34
2 13:56
4 15:04
That's what I did & failed:
SELECT t.attendanceID, t.seenPersonID, t.seenTime
(SELECT ROW_NUMBER() OVER (PARTITION BY seenID ORDER BY seenID) AS RowNo,
seenID,
seenPersonID,
seenTime
FROM personAttendances) t
WHERE t.RowNo=1
P.S: Notice SQL CE 4
If your seenTime increases as seenID increases:
select personID, min(seenTime) as seenTime
from personAttendances
group by personID
Update for another case:
If this is not the case, and you really want the seenTime that corresponds with the minimum seenID (assuming seenID is unique):
select a.personID, a.seenTime
from personAttendances as a
join (
-- Get the min seenID for each personID
select personID, min(seenID) as seenID
from personAttendances
group by personID
) as b on a.personID = b.personID
where a.seenID = b.seenID