Filtering rows with DateTime values that are within 1 minute

Question

I've got a simple table "Logins" with two columns:

1. username (nvarchar)
2. logged (datetime)

It's really simple, just records the username and datetime when someone logs into my web application. Sometimes, however, people login a few times in one minute... I want to try to run a query to filter those results and only have it return one row even if there are multiple logins in the same minute.

Here is an example:

(Results I get with a simple select)

username  logged
-------------------
kh0013    2010-08-16 21:29:21.020
tmt0006   2010-08-16 21:24:16.030
jrc0014   2010-08-16 21:17:37.187
jrc0014   2010-08-16 21:17:15.043
jrc0014   2010-08-16 21:17:00.593
jrm0017   2010-08-16 20:52:57.673
as0044    2010-08-16 20:45:51.210
snb0006   2010-08-16 20:33:29.873
weo0021   2010-08-16 19:54:57.093

As you can see, the user "jrc0014" logged in multiple times within the same minute. How can I write a query so that user is only logged once, like this:

(Desired Results)

username  logged
------------------
kh0013    2010-08-16 21:29:21.020
tmt0006   2010-08-16 21:24:16.030
jrc0014   2010-08-16 21:17:00.593
jrm0017   2010-08-16 20:52:57.673
as0044    2010-08-16 20:45:51.210
snb0006   2010-08-16 20:33:29.873
weo0021   2010-08-16 19:54:57.093

Answer 1

IN SQL Server, floor the datetime to the minute like this:

SELECT DISTINCT
    username , DATEADD(minute,DATEDIFF(minute,0,logged),0) AS logged
    FROM YourTable

Answer 2

Assuming SQL Server.

select username, logged
FROM logins l
WHERE NOT EXISTS(SELECT * FROM logins l2 
                 where l.username =l2.username 
                 AND DATEDIFF(SECOND,l2.logged,l.logged) < 60)

Answer 3

select username, logged
  from Logins o
 where not exists(select 1 
                    from Logins i
                   where i.username = o.username
                     and datediff(second, i.logged, o.logged) <= 60)
                 )

Answer 4

To get the output you listed, use:

  SELECT yt.username , 
         MAX(yt.logged) AS logged
    FROM YOUR_TABLE yt
GROUP BY yt.username, DATEADD(minute,DATEDIFF(minute,0,logged),0)