Duplicate records retrieval - 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 2005 Forums

Transact-SQL (2005)

Duplicate records retrieval

Author

Topic

pradeep_iete
Yak Posting Veteran

84 Posts

Posted - 2008-06-23 : 11:06:35

I want to remove duplication entries on the basis of compaparing
ApID,StID,CreationDate.If three match only i shoul get result set for deletion

ID ApID StID RoleID CreationDate
40 100 5 2 2008-01-31 20:27:03.850
41 101 5 2 2008-01-31 20:27:03.850
42 101 5 2 2008-01-31 20:27:03.850
901 1011 81 2 2008-01-31 20:27:03.850
902 1011 81 2 2008-01-31 20:27:03.850
903 1012 74 2 2008-01-31 20:27:03.850
3025 1013 4 2 2008-02-22 11:43:39.153
904 1014 74 2 2008-01-31 20:27:03.850
3002 1016 4 2 2008-02-22 11:28:34.513
3170 1017 94 2 2008-02-22 12:35:17.450
906 1019 4 2 2008-01-31 20:27:03.850
43 102 5 2 2008-01-31 20:27:03.850
907 1020 81 2 2008-01-31 20:27:03.850
908 1021 74 2 2008-01-31 20:27:03.850
3194 1022 74 2 2008-02-22 12:43:46.030
909 1023 81 2 2008-01-31 20:27:03.850
4750 1024 81 2 2008-05-23 14:27:45.317
912 1027 81 2 2008-01-31 20:27:03.850
4751 1028 81 2 2008-05-23 14:27:45.317
2638 103 5 2 2008-02-21 16:26:19.877
2571 103 150 128 2008-02-19 14:23:57.610
3205 1031 4 2 2008-02-22 12:47:32.653
915 1032 74 2 2008-01-31 20:27:03.850
3103 1039 81 2 2008-02-22 12:08:32.043
2639 104 5 2 2008-02-21 16:26:19.877
918 1040 74 2 2008-01-31 20:27:03.850
919 1041 4 2 2008-01-31 20:27:03.850
3206 1042 4 2 2008-02-22 12:47:50.903
920 1043 94 2 2008-01-31 20:27:03.850
922 1045 4 2 2008-01-31 20:27:03.850
3208 1046 4 2 2008-02-22 12:48:11.857
924 1048 4 2 2008-01-31 20:27:03.850
3209 1049 4 2 2008-02-22 12:48:30.687
2640 105 5 2 2008-02-21 16:26:19.877
926 1050 74 2 2008-01-31 20:27:03.850
3156 1051 5 2 2008-02-22 12:27:06.700
927 1052 94 2 2008-01-31 20:27:03.850
928 1053 74 2 2008-01-31 20:27:03.850
3098 1054 81 2 2008-02-22 12:07:00.000
3044 1055 5 2 2008-02-22 11:50:26.373
929 1056 4 2 2008-01-31 20:27:03.850
930 1058 81 2 2008-01-31 20:27:03.850
931 1059 94 2 2008-01-31 20:27:03.850
44 106 5 2 2008-01-31 20:27:03.850
3161 1060 4 2 2008-02-22 12:29:31.687
933 1061 74 2 2008-01-31 20:27:03.850
934 1062 94 2 2008-01-31 20:27:03.850
3174 1063 74 2 2008-02-22 12:36:24.170
935 1064 4 2 2008-01-31 20:27:03.850
936 1064 4 2 2008-01-31 20:27:03.850
938 1067 94 2 2008-01-31 20:27:03.850
939 1068 94 2 2008-01-31 20:27:03.850
3110 1069 4 2 2008-02-22 12:10:33.467
940 1070 74 2 2008-01-31 20:27:03.850
941 1070 74 2 2008-01-31 20:27:03.850
942 1070 74 2 2008-01-31 20:27:03.850
943 1070 74 2 2008-01-31 20:27:03.850
3219 1071 74 2 2008-02-22 12:51:09.687
944 1072 94 2 2008-01-31 20:27:03.850
945 1073 4 2 2008-01-31 20:27:03.850
3221 1074 81 2 2008-02-22 12:51:53.560
3222 1075 4 2 2008-02-22 12:52:18.373
946 1076 81 2 2008-01-31 20:27:03.850
947 1077 4 2 2008-01-31 20:27:03.850
3223 1078 4 2 2008-02-22 12:52:41.717
948 1079 94 2 2008-01-31 20:27:03.850
3220 1080 94 2 2008-02-22 12:51:30.467

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-23 : 11:09:35

[code]DELETE t
FROM (SELECT ROW_NUMBER() OVER(ApID,StID,CreationDate ORDER BY CreationDate) AS RowNo,*
FROM YourTable)t
WHERE t.RowNo >1[/code]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-23 : 13:18:06

quote:

Originally posted by visakh16

DELETE t
FROM (SELECT ROW_NUMBER() OVER(Partition by ApID,StID,CreationDate ORDER BY CreationDate) AS RowNo,*
FROM YourTable)t
WHERE t.RowNo >1

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources