I don't mind the approach but was I was looking for a PO within my database and found duplicate rows. Like, have a way to catch these.

What do you mean "catch"? Anyway, to delete duplicate rows, this is the typical solution: ; WITH numbering AS ( SELECT *, rn = row_number() OVER(PARTITION BY keycol ORDER BY something DESC) FROM tbl ) DELETE numbering WHERE rn > 1 In the PARTITION BY column, you list the column(s) where you don't want duplicates. In the ORDER BY clause you have the criteria for which row to keep. I added DESC, since commonly it is a date column, and you want to keep the most recent row. If you only want to view duplicate rows, you can do: ; WITH cnts AS ( SELECT *, cnt = COUNT(*) OVER(PARTITION BY keycol) FROM tbl ) SELECT * FROM cnts WHERE cnt > 1

And how do you define duplicates? Can't be by the primary key, because that key is unique.

Find duplicate rows in SQL

Accepted answer

Erland Sommarskog 111.4K Reputation points MVP

2024-07-30T20:23:46.1533333+00:00
What do you mean "catch"?

Anyway, to delete duplicate rows, this is the typical solution:

; WITH numbering AS ( SELECT *, rn = row_number() OVER(PARTITION BY keycol ORDER BY something DESC) FROM tbl ) DELETE numbering WHERE rn > 1

In the PARTITION BY column, you list the column(s) where you don't want duplicates. In the ORDER BY clause you have the criteria for which row to keep. I added DESC, since commonly it is a date column, and you want to keep the most recent row.

If you only want to view duplicate rows, you can do:

; WITH cnts AS ( SELECT *, cnt = COUNT(*) OVER(PARTITION BY keycol) FROM tbl ) SELECT * FROM cnts WHERE cnt > 1
Please sign in to rate this answer.
Jonathan Brotto 420 Reputation points

2024-07-30T20:39:17.9766667+00:00

Catch means finding duplicates within the same table. For example, row 3 and row 5 have the same records in the database. Would the approach work for that?

Erland Sommarskog 111.4K Reputation points MVP

2024-07-30T20:45:53.3366667+00:00

I have no idea, since I don't know what you mean by that. If one row is 3 and one row is 5, they have to be different, at least in the row number.

But try the queries.

Yitzhak Khabinsky 25,861 Reputation points

2024-07-30T20:51:14.39+00:00

Please provide a minimal reproducible example as in my original comments. Otherwise, it will be eternity before we will be able to help you if at all.

LiHongMSFT-4306 27,016 Reputation points

2024-07-31T02:05:58.1766667+00:00

Hi @Jonathan Brotto

Have you tried Erland's query which may solve your issue.

If still not solved, please post more details.

Jonathan Brotto 420 Reputation points

2024-08-01T14:08:24.9133333+00:00

Is there a more memory-efficient way of doing this?
(15 rows affected)

Msg 1105, Level 17, State 2, Line 9

Could not allocate space for object 'dbo.SORT temporary run storage: 143718385909760' in database 'tempdb' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

Completion time: 2024-08-01T09:30:01.9365124-04:00

Viorel 117.3K Reputation points

2024-08-01T14:50:52.66+00:00

Check if this alternative is more efficient:

select top(100) *, count(*) as number from Table group by Column1, Column2, Column3, Column4, Column5, Column6, Column7 having count(*) > 1

Erland Sommarskog 111.4K Reputation points MVP

2024-08-01T20:55:24.2066667+00:00

My guess is that Viorel's suggestion will meet the same fate.

Where is the database? On your laptop? On your server? The gist of the error message is that you need a bigger disk for tempdb. (Unless some smart person has set an upper limit for the space on tempdb.)

How big is the table? Post the output from "sp_spaceused yourtable".
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Find duplicate rows in SQL

All within the question as text, no images.

1 additional answer

Your answer