Find duplicate rows with PostgreSQL

by

Here is my take on it. 

select * from (
  SELECT id,
  ROW_NUMBER() OVER(PARTITION BY merchant_Id, url ORDER BY id asc) AS Row
  FROM Photos
) dups
where 
dups.Row > 1

Feel free to play with the order by to tailor the records you want to delete to your specification.

SQL Fiddle => http://sqlfiddle.com/#!15/d6941/1/0

SQL Fiddle for Postgres 9.2 is no longer supported; updating SQL Fiddle to postgres 9.3

More Related Contents:

  1. How to delete duplicate rows without unique identifier
  2. NOT NULL constraint over a set of columns
  3. What are the options for storing hierarchical data in a relational database?
  4. Is there any rule of thumb to construct SQL query from a human-readable description?
  5. How can I list the tables in a SQLite database file that was opened with ATTACH?
  6. In MySQL queries, why use join instead of where?
  7. Doing a WHERE .. IN subquery in Doctrine 2
  8. Transpose latest rows per user to columns
  9. T-SQL Cast versus Convert
  10. Is there any boolean type in Oracle databases?
  11. A beginner’s guide to SQL database design [closed]
  12. Physical vs. logical (hard vs. soft) delete of database record? [closed]
  13. SQLite – ORDER BY RAND()
  14. The Next-gen Databases [closed]
  15. SQL Between clause with strings columns
  16. Insert an image in postgresql database
  17. Do database transactions prevent race conditions?
  18. Database EAV Pros/Cons and Alternatives
  19. Conditional logic in PostDeployment.sql script using SQLCMD
  20. Is there a difference between Select * and Select [list each col] [duplicate]
  21. Does an empty SQL table have a superkey? Does every SQL table have one?
  22. How to sum up time field in SQL Server
  23. How to show row numbers in PostgreSQL query?
  24. SQLite3 UNIQUE constraint failed error
  25. How to do an INNER JOIN on multiple columns
  26. SQL: Using NULL values vs. default values
  27. Why don’t DBMS’s support ASSERTION
  28. Temporal database design, with a twist (live vs draft rows)
  29. Executing SQL query on multiple databases
  30. How to bulk insert only new rows in PostreSQL

Leave a Comment