removing duplicate records - 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

SQL Server Development (2000)

removing duplicate records

Author

Topic

PeterG
Posting Yak Master

156 Posts

Posted - 2003-07-25 : 12:42:46

How do I write the query that would display everything and filter out the record with the latest lngID for those with more than 1 account? For example, only 1 record (lngId=145513) should show for SCEDELI, DARREN.

lngID txtName txtAccountNum txtCompanyNum txtUserId txtInternetCompNum

58852 SAELD, MARK 0000000000059857 213601657 salkeldm 1244
58844 SAASIN, EUGENE 0000000000059849 213601657 sarrasine 1244
58881 SCAB, JOCELYN 0000000000059886 1244 schaabj 1244
111181 SCEDELI, DARREN 0000000000096656 213601657 SCHAEDELID 1244
145513 SCEDELI, DARREN 0000000000126063 213601657 SCHAEDELIDA 1244
119149 SCIDT, VAUGHN 0000000000103981 1244 ARMSTRONGR 1244
127107 SCIDT, VAUGHN 0000000000111768 1244 PHIPPSLE 1244
145510 SCIDT, VAUGHN 0000000000126584 1244 SCHMIDTVA 1244
60591 SHPE, NIALL 0000000000061440 213601657 sharpen 1244
60594 SHCHUK, TRENT 0000000000061443 213601657 shewchukt 1244
83360 SHCHUK, WILDEN 0000000000076342 213601657 SHEWCHUKW 1244
58865 SOMON, DAVE 0000000000059870 213601657 solomond 1244
151307 SOERVILLE, MICHAEL 0000000000129226 213601657 NULL 1244
119146 SPZ, NATACHA 0000000000103978 1244 WALDRONK 1244
119888 SPZ, NATACHA 0000000000104720 1244 SPETZN 1244
58879 STWAY, BRIAN 0000000000059884 213601657 stanwayb 1244
58847 STPE, KEN 0000000000059852 213601657 stoupek 1244
58842 STM, LANCE 0000000000059847 213601657 stroml 1244
58839 SUEN, DARYL 0000000000059844 213601657 sugdend 1244
58886 SWAMER, GREG 0000000000059891 213601657 swinamerg 1244

SamC
White Water Yakist

3467 Posts

Posted - 2003-07-25 : 13:07:40

SELECT *

FROM MyTable

WHERE lngID IN (

SELECT MAX(lngID ) as lngID -- Assuming MAX is latest?

FROM MyTable

GROUP BY txtName

)

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-25 : 13:37:38

That's pretty arbitrary. You've got to different accounts. Which one do you want to use?

And to prevent this you need to add a unique constraint, so it doesn't happen again

(Or we could get in to the whole FIRST() thing again)

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources