Duplicate rows

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...

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);

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....

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]

Thanx all for your post....jurij,pando it is working now.