-
Duplicate rows question
I'm trying to get all duplicates from a table.
there are two columns that are the same, issueid and quantity.
When I run the following, I only get one of the duplicate rows, not all of them....
select issueid,quantity
from lot
where trunc(createdate)=TO_DATE('06/16/2003','MM/DD/YYYY') or
trunc(createdate)=TO_DATE('06/17/2003','MM/DD/YYYY')
group by issueid,quantity
having count(*) > 1
Any ideas how to retrieve all the duplicate rows?
thanks
Last edited by rgaskell; 06-24-2003 at 05:57 AM.
-
ex: use rowid concatenated with the duplicated key portion of the table:
SELECT * FROM loader_physician_recs
WHERE physician_id||rowid IN
(SELECT a.physician_id||a.rowid
FROM loader_physician_recs a, loader_physician_recs b
WHERE a.physician_id = b.physician_id
AND a.rowid < b.rowid);
Gregg
-
Code:
select
*
from
lot
where
(issueid,quantity) in
(
select
issueid,
quantity
from
lot
where
trunc(createdate) = TO_DATE('06/16/2003','MM/DD/YYYY') or
trunc(createdate) = TO_DATE('06/17/2003','MM/DD/YYYY')
group by
issueid,
quantity
having
count(*) > 1
)
-
I think u can avoid OR clause.
Code:
select
*
from
lot
where
(issueid,quantity) in
(
select
issueid,
quantity
from
lot
where
trunc(createdate) in
(TO_DATE('06/16/2003','MM/DD/YYYY'),TO_DATE('06/17/2003','MM/DD/YYYY'))
group by
issueid,
quantity
having
count(1) > 1
)
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
Code:
select *
from lot l,
(select issueid, quantity
from lot
where createdate >= TO_DATE('06/16/2003','MM/DD/YYYY')
and createdate < TO_DATE('06/18/2003','MM/DD/YYYY')
group by issueid, quantity
having count(1) > 1 ) d
where l.issueid = d.issueid
and l.quantity = d.quantity
Tomaž
"A common mistake that people make when trying to design something completely
foolproof is to underestimate the ingenuity of complete fools" - Douglas Adams
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|