How to view only rows with duplicate values in a table

[Gianni]

Referring to a question posted here, this is a possible SQL query that should do:

supposed you have an sqlite table named 'multimedia' with a column named 'sha1_hash', and you want to see only rows with the 'sha1_hash' value that appears more than one time within the table, you could use an SQL query like this:

SELECT sha1_hash, COUNT(sha1_hash) AS sha1_sum FROM multimedia GROUP BY sha1_hash HAVING sha1_sum>1 ORDER BY sha1_sum DESC;

this query should show only records with the value of sha1_hash that appears more times also in other records, showing it only one time, with a column indicating how many occurrences are present, ordered from the most recurring to the least recurring one.

I don't know if this query can be simplified a bit...?,

.... if there is a simpler way I would like some hint,
Thanks

Edited June 23, 2016 by Chimp
corrections in title and text

[Danp2]

That's how I would do it. Not sure how you could get any simpler...

[Gianni]

  On 6/23/2016 at 12:54 AM, Danp2 said:

That's how I would do it. Not sure how you could get any simpler...

Expand  

.... thanks ....

Edited June 23, 2016 by Chimp

[Skysnake]

What will happen to all items with same hash? Obviously there must remain one item and the rest deleted?

-- original

SELECT sha1_hash, COUNT(sha1_hash) AS sha1_sum FROM multimedia GROUP BY sha1_hash HAVING sha1_sum>1 ORDER BY sha1_sum DESC;

-- my take
--delete from multimedia
select sha1_hash from multimedia
where id not in ( select min(id)
from multimedia
group by sha1_hash )

Yes / No / Maybe ?

Edited June 23, 2016 by Skysnake