-
Hi all,
I've got a problem. Consider a table DEPT
the table structure is given below
deptno NUMBER NOT NULL
dname VARCHAR2(20)
loc VARChAR2
consider the content of dept table
18:06:45 ANDY2> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS KOLKATA
50 SALES INDIA
Now what i want a query which displays all the contents but the duplicate entries shd be displayed only once...
that is the o/p shd be like this
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS KOLKATA
i've used this query which displays only the non duplicate values
select dname,deptno from dept where dname in (
select dname from dept
group by dname
having count(dname)=1)
but i cannot proceed further. kindly help...
Aniruddha Gupta
-
From your example its not clear what you are trying to do.
If you are trying to remove duplicate rows then try:
select distinct * from dept;
--
Paul
-
Where ever you see join criteria in the following examples between outer and sub selects, put your primary key in:
delete from TABLEA a
where rowid not in
(select min(rowid) from TABLEA b
where a.col1 = b.col1
and a.col2 = b.col2);
--------------------------------------------------------------
Also try this
1) To display duplicate records using the following query
Select col1, col2, count(*) duplicates
from tablename
group by col1, col2
having count(*) > 1;
2) To delete duplicate record use the following query
DELETE from tablename a
WHERE ROWID NOT IN (SELECT MIN(ROWID)
FROM tablename b
where a.col1 = b.col1
and a.col2 = b.col2);
-
duplicate
well i'll be more clear it is not that i want check duplicates or eemove them ...what i want is consider this
18:06:45 ANDY2> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS KOLKATA
50 SALES INDIA
here deptno 30 and 50 have the same dname "SALES" ...what i want the o/p of my query to be is, out of these row only one row shd appear (ie if there is more than one occurance of dname the o/p of query should give me only single occurance...... and other dname shd appear as they are.)
ie....that the o/p shd be
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS KOLKATA
ie as deptno --> 50 dname--> is SALES which is same as deptno-->30 .
only corresponding row for deptno 30 is shown ..
hope i am clear enough....
Aniruddha Gupta
-
Code:
select *
from (select a.*,
row_number() over (partition by dname order by
deptno) numrow
from dept a) b
where b.numrow = 1
select *
from (select a.*,
dense_rank() over (partition by dname order by
deptno) numrow
from dept a) b
where b.numrow = 1
select *
from (select a.*,
rank() over (partition by dname order by
deptno) numrow
from dept a) b
where b.numrow = 1
up to you, be careful with rank and dense_rank
-
If analytical functions are not available to you, use the following:
SELECT * FROM dept d1 WHERE NOT EXISTS
(SELECT NULL FROM dept d2
WHERE d2.dname = d1.dname AND d2.ROWID > d1.ROWID);
Hopefuly your DNAME column is indexed if this is a large table...
[Edited by jmodic on 08-14-2002 at 04:37 AM]
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
Duplicate
Thanx all for your post....jurij,pando it is working now.
Aniruddha Gupta
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
|