Alex Rivera | Logout

SQL: Return only first occurrence

Asked 2012-01-04T15:28:10.750
34

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

Edit
Report

1 Answer

21

You're making it way too difficult:

select personID, min(seenTime)
from personAttendances
group by personID
answered 2012-01-04T15:31:42.867

Your Answer