Display all duplicate records based multiple columns

select var1, var2, var3
from (
      select var1,
             var2,
             var3,
             count(*) over(partition by var2, var3) as dc
      from YourTable
     ) as T
where dc > 1

var1 var2 var3
---- ---- ----
a    a    a
b    a    a
c    a    a

select var1, var2, var3
from (
      select var1,
             var2,
             var3,
             row_number() over(partition by var2, var3 order by var1) as rn
      from YourTable
     ) as T
where rn > 1

var1 var2 var3
---- ---- ----
b    a    a
c    a    a

class Model

  def self.dedupe
    # find all models and group them on keys which should be common
    grouped = all.group_by{|model| [model.name,model.year,model.trim,model.make_id] }
    grouped.values.each do |duplicates|
      # the first one we want to keep right?
      first_one = duplicates.shift # or pop for last one
      # if there are any more left, they are duplicates
      # so delete all of them
      duplicates.each{|double| double.destroy} # duplicates can now be destroyed
    end
  end

end

Model.dedupe

SELECT O.EMP_ID 
FROM ORDERS O 
GROUP BY O.EMP_ID  
HAVING COUNT(distinct O.ITEM_ID) <> COUNT(*)

Product.where.not(
  id: Product.group(:legacy_id, :company).pluck('min(products.id)')
).delete_all

delete from products
where id not in ( 
   select min(p.id) from products p group by p.legacy_id, p.company
)

SELECT fullname,
       address,
       city
FROM   (SELECT *,
               COUNT(*) OVER (PARTITION BY fullname, city) AS cnt
        FROM   employee) e
WHERE  cnt > 1