sql query to find the duplicate records

Question

what is the sql query to find the duplicate records and display in descending  based on the highest count and the id display the records   for example   getting the count can be done with   select title  count title  as cnt from kmovies group by title order by cnt desc   and the result will be like   title cnt  ravi   10 prabhu  9 srinu   6   now what is the query to get the result like below   ravi ravi ravi    10 times prabhu prabhu  9 times srinu srinu   6 times

User · Answer

You can t do it as a simple single query  but this would do   select title from kmovies where title in       select title     from kmovies     group by title     order by cnt desc     having count title   gt  1

User · Answer

If your RDBMS supports the OVER clause     SELECT    title FROM           select        title  count    OVER  PARTITION BY title  as cnt     from       kmovies       T ORDER BY    cnt DESC

User · Answer

This query uses the Group By and and Having clauses to allow you to select  locate and list out  for each duplicate record  The As clause is a convenience to refer to Quantity in the select and Order By clauses  but is not really part of getting you the duplicate rows   Select     Title      Count  Title   As  Quantity     From     Training    Group By     Title    Having      Count  Title    gt  1    Order By     Quantity desc

User · Answer

select distinct title                   select count title                  from kmovies as sub                 where sub title kmovies title  as cnt  from kmovies  group by title  order by cnt desc

User · Answer

You can do it in a single query    Select t Id  t title  z dupCount From yourtable T Join     select title  Count     dupCount     from yourtable      group By title     Having Count     gt  1  z    On z title   t Title order By dupCount Desc

[sql] sql query to find the duplicate records

Examples related to sql

Examples related to duplicates