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

Answer 1

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