-- Find duplicate rows
--
select rowid from DBSTAR_REQUESTED_ITEM a
where a.rowid > (select min(b.rowid)
                    from DBSTAR_REQUESTED_ITEM b
                    where b.request_id = a.request_id 
                    and b.program_id = a.program_id
                    and b.req_id = a.req_id
                    and b.requested_date = a.requested_date
)
;

select distinct req_id from DBSTAR_REQUESTED_ITEM c where exists
(
select rowid from DBSTAR_REQUESTED_ITEM a
where a.rowid > (select min(b.rowid)
                    from DBSTAR_REQUESTED_ITEM b
                    where b.req_id = a.req_id)
and c.req_id = a.req_id
)
;

select count(*) from DBSTAR_REQUESTED_ITEM a
where a.rowid > (select min(b.rowid)
                    from DBSTAR_REQUESTED_ITEM b
                    where b.req_id = a.req_id
)
;

select key, count(*)
from table
group by key
having count(*) > 1;
--
select program_id,category_id,item_id, count(*)
from item
group by program_id,category_id,item_id
having count(*) > 1;
--


-- Delete duplicate rows from a table	
--
DELETE FROM EMP E
WHERE  E.ROWID > ( SELECT MIN(X.ROWID)
                   FROM   EMP X
                   WHERE  X.EMP_NO = E.EMP_NO );

DELETE FROM DBSTAR_REQUESTED_ITEM ri1
WHERE  ri1.ROWID > (SELECT MIN(ri2.ROWID)
                   FROM   DBSTAR_REQUESTED_ITEM ri2
                   WHERE  ri1.req_id = ri2.req_id)
and ri1.req_id < 100000
;

DELETE FROM DBSTAR_REQUESTED_ITEM ri1
WHERE  ri1.ROWID > (SELECT MIN(ri2.ROWID)
                   FROM   DBSTAR_REQUESTED_ITEM ri2
                   WHERE  ri1.req_id = ri2.req_id)
and ri1.req_id >= 100000 and ri1.req_id < 200000
;

DELETE FROM DBSTAR_REQUESTED_ITEM ri1
WHERE  ri1.ROWID > (SELECT MIN(ri2.ROWID)
                   FROM   DBSTAR_REQUESTED_ITEM ri2
                   WHERE  ri1.req_id = ri2.req_id)
and ri1.req_id >= 200000 and ri1.req_id < 300000
;

DELETE FROM item i1
WHERE  i1.ROWID > (SELECT MIN(i2.ROWID)
                   FROM   item i2
                   WHERE  i1.program_id = i2.program_id
						and i1.category_id = i2.category_id
 						and i1.item_id = i2.item_id)
;