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
This question is related to
sql
duplicates
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 ) > 1
Order By
Quantity desc
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(*) > 1) z
On z.title = t.Title
order By dupCount Desc
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
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) > 1
)
Source: Stackoverflow.com