sqlsql-server-cesql-server-ce-4

SQL: Return only first occurrence


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


Solution

  • 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