Select statement to find duplicates on certain fields?

To get the list of fields for which there are multiple records, you can use select field1,field2,field3, count(*) from table_name group by field1,field2,field3 having count(*) > 1 Check this link for more information on how to delete the rows support.microsoft.com/kb/139444 Edit : As the other users mentioned, there should be a criterion for deciding how you define "first rows" before you use the approach in the link above. Based on that you'll need to use an order by clause and a sub query if needed. If you can post some sample data, it would really help.

To get the list of fields for which there are multiple records, you can use.. select field1,field2,field3, count(*) from table_name group by field1,field2,field3 having count(*) > 1 Check this link for more information on how to delete the rows. support.microsoft.com/kb/139444 Edit : As the other users mentioned, there should be a criterion for deciding how you define "first rows" before you use the approach in the link above. Based on that you'll need to use an order by clause and a sub query if needed.

If you can post some sample data, it would really help.

You mention "the first one", so I assume that you have some kind of ordering on your data. Let's assume that your data is ordered by some field ID. This SQL should get you the duplicate entries except for the first one.It basically selects all rows for which another row with (a) the same fields and (b) a lower ID exists.

Performance won't be great, but it might solve your problem. SELECT A.ID, A. Field1, A.

Field2, A. Field3 FROM myTable A WHERE EXISTS (SELECT B. ID FROM myTable B WHERE B.

Field1 = A. Field1 AND B. Field2 = A.

Field2 AND B. Field3 = A. Field3 AND B.

ID ID).

If you're using SQL Server 2005 or later (and the tags for your question indicate SQL Server 2008), you can use ranking functions to return the duplicate records after the first one if using joins is less desirable or impractical for some reason. The following example shows this in action, where it also works with null values in the columns examined. Create table Table1 ( Field1 int, Field2 int, Field3 int, Field4 int ) insert Table1 values (1,1,1,1) , (1,1,1,2) , (1,1,1,3) , (2,2,2,1) , (3,3,3,1) , (3,3,3,2) , (null, null, 2, 1) , (null, null, 2, 3) select * from (select Field1 , Field2 , Field3 , Field4 , row_number() over (partition by Field1 , Field2 , Field3 order by Field4) as occurrence from Table1) x where occurrence > 1 Notice after running this example that the first record out of every "group" is excluded, and that records with null values are handled properly.

If you don't have a column available to order the records within a group, you can use the partition-by columns as the order-by columns.

This is a fun solution with SQL Server 2005 that I like. I'm going to assume that by "for every record except for the first one", you mean that there is another "id" column that we can use to identify which row is "first". SELECT id , field1 , field2 , field3 FROM ( SELECT id , field1 , field2 , field3 , RANK() OVER (PARTITION BY field1, field2, field3 ORDER BY id ASC) AS rank FROM table_name ) a WHERE rank > 1.

Just noticed the SQL Server 2008 tag. Glad my suggestion is still valid. – Norla Dec 13 '10 at 23:04.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.