0

I am have a table containing 63 columns.

I am looking to find exact duplicate rows (all 63 columns) from this table, in mysql.

How do I achieve this?

Shadow
  • 33,525
  • 10
  • 51
  • 64
JBhatt
  • 53
  • 2
  • 11

1 Answers1

0

There is no easy way to handle this as far as I know. One option would just be a brute force GROUP BY query:

SELECT col1, col2, ..., col63
FROM yourTable
GROUP BY col1, col2, ..., col63
HAVING COUNT(*) > 1;

The above query would generate one record per offending duplication. If you wanted to see how many records were in duplicate, you could add COUNT(*) to the select clause.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • I was hoping if I could avoid putting 63 columns in the above query. Is there a way other than above? – JBhatt Sep 04 '20 at 12:35
  • Not without resorting to some sort of dynamic SQL. By "dynamic" here I mean doing something like writing a code which auto generates that list of all columns. By the way, while sometimes you might require this many columns, in general, you should avoid having tables with too many columns. – Tim Biegeleisen Sep 04 '20 at 12:36