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

5 Answers

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

1

You're making it way too difficult:

select personID, min(seenTime)
from personAttendances
group by personID

0

for PostgreSQL there is DISTINCT ON

2

You need to order by seen time not by seen id:

PARTITION BY seenID ORDER BY seenTime

Add this to your SQL:

and where not exists (select 1 from personAttendances t2 where t.personID=t2.personID and t2.seenID < t.seenID)

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy