Duplicate entries - 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)

Duplicate entries

Author

Topic

mdhingra01
Posting Yak Master

179 Posts

Posted - 2004-03-22 : 13:33:57

I hope I can explain this one correctly...

I have 4 tables (tmpcurHRMIS,tmpcurHRMIS1,tmpcurHRMIS2,tmpcurHRMIS3).

All tables have exactly same structre (31 columns).
[Extracted, Unit_Coll, Pos_Number, Post_Date, Comm_Servi, Effe_Servi, Old_Reg, Rank_date, ERank, Surname, Initials, Sex, First_Lang, Read_Date, Write_Date, Oral_Date, Psreg, Regimental,FUC, Empl_Class, Years_serv, Memb_1, Memb_2, Memb_3, Memb_4, Memb_5, Pos_Status, B_Empl, Emp_Stat, Action, Act_Reason, Time_Ingrc, Time_Inpos, Time_Inccs, Empl_Id]

tmpcurHRMIS is the master table with 45280 records and should have 45183 records (+97 records), tmpcurHRMIS1 is a subset of tmpcurHRMIS and has 4066 records. tmpcurHRMIS2 is a subset of tmpcurHRMIS and has 41214 records. tmpcurHRMIS3 is a subset of tmpcurHRMIS2 and has 41214 and should have 41117 (+97) records.

tmpcurHRMIS1 is created with the statement:
Select *
from tmpCurhrmis
where left(surname,10) = '*** VACANT'

tmpCurhrmis2 is created with the statement:
select *
from tmpCurhrmis
where left(surname,10) <> '*** VACANT'

tmpCurhrmis3 is created with the statement:
select *
from tmpCurhrmis2

There are 97 records that have mulitple entries. I am trying to filter the insert so the one with the max rank_date is the only record inserted. Can anyone help?

X002548
Not Just a Number

15586 Posts

Posted - 2004-03-22 : 13:43:28

OK, so when you say dup, you don't reallly mean dup, because the 97 "dup" rows have a different rank date....

What other columns are not the same..

Better yet, which columns do you consider to be a "dup"

SELECT Collist (
SELECT Collist From tmpcurHRMIS
UNION ALL
SELECT Collist From tmpcurHRMIS1
UNION ALL
SELECT Collist From tmpcurHRMIS2
UNION ALL
SELECT Collist From tmpcurHRMIS3
)
GROUP BY dup cols
HAVING COUNT(*)=4

To find the offending rows.....

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources