How can I check and remove duplicate rows?

Question

Have problem with quite big table, where are some null values in 3 columns - datetime2 (and 2 float columns).

Nice simple request from similar question returns only 2 rows where datetime2 is null, but nothing else (same as lot of others):

DELETE FROM MyTable
LEFT OUTER JOIN (
   SELECT MIN(RowId) as RowId, allRemainingCols
   FROM MyTable 
   GROUP BY allRemainingCols
) as KeepRows ON
   MyTable.RowId = KeepRows.RowId
WHERE
   KeepRows.RowId IS NULL

Seems to work without datetime2 column having nulls ??

There is manual workaround, but is there any way to create request or procedure using TSQL only ?

SELECT id,remainingColumns
FROM table
order BY remainingColumns

Compare all columns in XL (15 in my case, placed =ROW() in first column as a check and formula next to last column + auto filter for TRUEs): =AND(B1=B2;C1=C2;D1=D2;E1=E2;F1=F2;G1=G2;H1=H2;I1=I2;J1=J2;K1=K2;L1=L2;M1=M2;N1=N2;O1=O2;P1=P2)

Or compare 3 rows like this and select all non-unique rows

=OR(
AND(B1=B2;C1=C2;D1=D2;E1=E2;F1=F2;G1=G2;H1=H2;I1=I2;J1=J2;K1=K2;L1=L2;M1=M2;N1=N2;O1=O2;P1=P2);
AND(B2=B3;C2=C3;D2=D3;E2=E3;F2=F3;G2=G3;H2=H3;I2=I3;J2=J3;K2=K3;L2=L3;M2=M3;N2=N3;O2=O3;P2=P3)
)

Have a look at this solution instead, which doesn't use a `JOIN`: [How to delete duplicate rows in SQL Server?](https://stackoverflow.com/q/18390574/2029983) — Thom A, Sep 25 '20 at 08:26
Either I cannot reproduce your issue ([fiddle](https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=27db03b751e7b6c7b203208c5e5c7e37)) or I am not understanding your question. Please provide some sample data and the expected output. — Sander, Sep 25 '20 at 09:28
Most likely you do not really understand how floating point values work in a binary system and that is the reason you struggle. To compare floats you generally use a range to know if 2 numbers are "equal" or convert to a specific accuracy and then compare. It does not help to post pseudo-code without labelling as such ("GROUP BY allRemainingCols"). If you join on RowID, then why do you need to compare at all? Something seems off here. — SMor, Sep 25 '20 at 11:37

score 0 · Answer 1 · answered Sep 25 '20 at 13:38

Quite much work to find my particular data/answer...

Most of float numbers were slightly different.

Hard to find, but simple CAST(column as binary) can show these invisible differences...

Like 96,6666666666667 vs 0x0000000000000000000000000000000000000000000040582AAAAAAAAAAD vs 0x0000000000000000000000000000000000000000000040582AAAAAAAAAAB etc.

And visible 96.6666666666667 can return something different way again: 0x0000000000000000000000000000000000000F0D0001AB6A489F2D6F0300

How can I check and remove duplicate rows?

1 Answers1