----------------------------------------------
-- finding duplicate titles in DSpace metadata

-- http://www.xaprb.com/blog/2006/10/09/how-to-find-duplicate-rows-with-sql
SELECT item_id, text_value
INTO TEMPORARY my_query
FROM metadatavalue
WHERE metadata_field_id = 64
GROUP BY text_value
HAVING COUNT(*) > 1
LIMIT 30;

SELECT 'http://hdl.handle.net/'||handle AS url, text_value
FROM my_query, handle
WHERE item_id = resource_id;



SELECT COUNT(text_value), text_value
FROM metadatavalue
WHERE metadata_field_id = 64
GROUP BY text_value
HAVING COUNT(*) > 1;

-- http://www.mximize.com/how-to-find-duplicate-values-in-a-table-
SELECT item_id, text_value
INTO TEMPORARY my_query
FROM metadatavalue
WHERE text_value IN (
        SELECT text_value
        FROM metadatavalue
        WHERE metadata_field_id = 64
        GROUP BY text_value
        HAVING COUNT(*) > 1
)
ORDER BY text_value;

SELECT 'http://hdl.handle.net/'||handle AS url, text_value
FROM my_query, handle
WHERE item_id = resource_id;

-- with extra metadata columns
SELECT item_id, text_value AS nazev
INTO TEMPORARY my_query
FROM metadatavalue
WHERE text_value IN (
        SELECT text_value
        FROM metadatavalue
        WHERE metadata_field_id = 64
        GROUP BY text_value
        HAVING COUNT(*) > 1
);

SELECT my_query.item_id, nazev, text_value AS autor
INTO TEMPORARY my_query2
FROM my_query, metadatavalue
WHERE my_query.item_id = metadatavalue.item_id
AND metadata_field_id = 3;

SELECT my_query2.item_id, nazev, autor, text_value AS typ
INTO TEMPORARY my_query3
FROM my_query2, metadatavalue
WHERE my_query2.item_id = metadatavalue.item_id
AND metadata_field_id = 83;

SELECT 'http://hdl.handle.net/'||handle AS url, typ, autor, nazev
FROM my_query3, handle
WHERE item_id = resource_id
ORDER BY nazev;