compare/delete query

[BlueFalcon]

I need a search a table for duplicate entries(login ID). When I fin
duplicate entries, I need to compare the "Login Time" and keep th
record that has the earliest time.

So if my table was like the following:
LoginID LoginTime
2396 7:02am
2589 7:00AM
2396 12:50PM
5845 8:05AM
2396 7:35AM

I would need to delete the two later entries for 2396.

In the end, I need a table without duplicates and the earliest logi
times.

Can anyone assist me? Thanks in advance

 

[fofa]

You could try something like this:
delete T1.* from MyTable T1
where T1.loginid in (select T2.loginid from Mytable T2 group b
T2.loginid having count(*) > 1)
and T1.LoginTime <> (select min(T3.logintime) from MyTable T3 grou
by T3.LoginID where T3.loginID = T1.LoginID

 

[fofa]

You could try something like this:
delete T1.* from MyTable T1
where T1.loginid in (select T2.loginid from Mytable T2 group b
T2.loginid having count(*) > 1)
and T1.LoginTime <> (select min(T3.logintime) from MyTable T3 grou
by T3.LoginID where T3.loginID = T1.LoginID

 

[BlueFalcon]

I took your suggestion and this is how I plugged it into the sql view i
access:

delete T1.* from tblCMSTest T1
whereT1.extension in (select T2.loginid from tblCMSTest T2 group b
T2.extension having count(*) >1)
and T1.login <> (select min(T3.login) from tblCMSTest T3 group b
T3.extension where T3.extension = T1.extension)

It came back with an error in syntax for the FROM statement.

I'm not familiar with sql enough to know what to do. Any help would b
appreciated

 

[BlueFalcon]

I took your suggestion and this is how I plugged it into the sql view i
access:

delete T1.* from tblCMSTest T1
whereT1.extension in (select T2.loginid from tblCMSTest T2 group b
T2.extension having count(*) >1)
and T1.login <> (select min(T3.login) from tblCMSTest T3 group b
T3.extension where T3.extension = T1.extension)

It came back with an error in syntax for the FROM statement.

I'm not familiar with sql enough to know what to do. Any help would b
appreciated

 

[John Vinson]

I took your suggestion and this is how I plugged it into the sql view in
access:

delete T1.* from tblCMSTest T1
whereT1.extension in (select T2.loginid from tblCMSTest T2 group by
T2.extension having count(*) >1)
and T1.login <> (select min(T3.login) from tblCMSTest T3 group by
T3.extension where T3.extension = T1.extension)

It came back with an error in syntax for the FROM statement.

Uf this is copied and pasted it looks like you have a missing blank
after the word WHERE. Just for clarity I'd also suggest using the AS
keyword when defining an alias:

delete T1.* from tblCMSTest AS T1
where T1.extension in (select T2.loginid from tblCMSTest AS T2 group
by T2.extension having count(*) >1)
and T1.login <> (select min(T3.login) from tblCMSTest AS T3 group by
T3.extension where T3.extension = T1.extension)