smallest date query

[Jezza]

I have a sessions table in my SQL2000 database which lists the current logged on sessions. I have found that some users have logged on twice, some legitimately and possibly others who have not, the cut down table would look like this:

Fred.....2010-11-12 09:05:01.200
Bob......2010-11-12 10:05:01.200
Fred.....2010-11-12 08:11:01.200


As you can see Fred's account is logged on twice, I would like to extract the user's name and log on time (second column) using a query from the table that is the earliest date or time in this example:

Fred.....2010-11-12 08:11:01.200

But I do not want the records where the user is only logged in once. I think that makes sense but please write back if you need clarification.

[HansV]

Let's say the table is named tblLogon with fields UserName and Logon. Create a totals query like this:

SELECT UserName, Min(Logon) AS EarliestLogon
FROM tblLogon
GROUP BY UserName
HAVING Count(UserName)>1

[Jezza]

Thank you

[Jezza]

Sorry as an addition to this requirement, how would I delete these particular records as I can't seem to get the syntax right?

[HansV]

What if someone has logged in 3 times - do you want to delete only the earliest time, or all but the most recent one?

[Jezza]

Thanks for getting back as I have just found that problem exists, I think it best to say delete all but the most recent one.

[HansV]

With the same names as before:

DELETE Logon
FROM tblLogon
WHERE Logon<(SELECT Max(t.Logon) FROM tblLogon AS t WHERE t.UserName=tblLogon.UserName)

The alias t is required to distinguish between the username in the subquery and that from the main query.

[Jezza]

That's the baby, thanks