delete duplicate - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

delete duplicate

Author

Topic

jung1975
Aged Yak Warrior

503 Posts

Posted - 2004-07-14 : 16:57:25

select * from login
join login_h on login.login_id = login_h.login_id return


Loginid     time                refid
26692	2004-04-07 21:12:18.233	3268
26692	2004-04-07 21:22:36.107	3268
44657	2004-04-13 14:45:16.560	3909
44657	2004-04-13 14:45:16.560	18868
45138	2004-04-13 19:47:31.840	18869

I would like to remove the duplicate time and select only single row

Loginid time refid count
26692 2004-04-07 21:12:18.233 3268 1
26692 2004-04-07 21:22:36.107 3268 1
44657 2004-04-13 14:45:16.560 3909 1
45138 2004-04-13 19:47:31.840 18869 1

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2004-07-14 : 21:30:29

What is the business rule that determines which refid to display ?
And what table is refid in ?

You could add a subquery in the where clause like :

WHERE refid = (SELECT min(refid) FROM table WHERE loginid = login.loginid and time = login.time)

You'll have to customise that to suit, you haven't given enough info for me to do it exactly.

Damian

Kristen
Test

22859 Posts

Posted - 2004-07-14 : 22:04:24

Could it be just a


SELECT Loginid, time, MIN(refid), count(*)
from login
join login_h on login.login_id = login_h.login_id return
GROUP BY Loginid, time

Kristen

maodou
Starting Member

5 Posts

Posted - 2004-07-19 : 03:00:47

I think 'select distinct *'

only test,have bug

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources